Thread: adding on delete cascade constraint?
Currently I have a table defined as such: TAL=# \d internal_keywords Table "public.internal_keywords" Column | Type | Modifiers ---------+---------+----------- keyword | text | not null pid | integer | not null Indexes: internal_keywords_pkey primary key btree (keyword, pid) Foreign Key constraints: $1 FOREIGN KEY (pid) REFERENCES products(id) ON UPDATE NO ACTION ON DELETE NO ACTION How can I change the ON DELETE action to CASCADE for column pid? I've check the alter table documentation but cannot find any reference to this. Thanks, Jean-Christian Imbeault
On Fri, 2003-09-19 at 11:09, Jean-Christian Imbeault wrote: > Currently I have a table defined as such: > > TAL=# \d internal_keywords > Table "public.internal_keywords" > Column | Type | Modifiers > ---------+---------+----------- > keyword | text | not null > pid | integer | not null > Indexes: internal_keywords_pkey primary key btree (keyword, pid) > Foreign Key constraints: $1 FOREIGN KEY (pid) REFERENCES products(id) ON > UPDATE NO ACTION ON DELETE NO ACTION > > How can I change the ON DELETE action to CASCADE for column pid? > > I've check the alter table documentation but cannot find any reference > to this. Drop the constraint; then add an amended one. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Bring ye all the tithes into the storehouse, that there may be meat in mine house, and prove me now herewith, saith the LORD of hosts, if I will not open you the windows of heaven, and pour you out a blessing, that there shall not be room enough to receive it." Malachi 3:10
Oliver Elphick wrote: >>Foreign Key constraints: $1 FOREIGN KEY (pid) REFERENCES products(id) ON >>UPDATE NO ACTION ON DELETE NO ACTION > > Drop the constraint; then add an amended one. Ok, how do I drop the constraint, it has no name. The documentation is rather poor on how to get the name of unamed constraints: "To remove a constraint you need to know its name. If you gave it a name then that's easy. Otherwise the system assigned a generated name, which you need to find out. The psql command \d tablename can be helpful here" Secondly what is the correct syntax for adding a new constraint with ON DELETE CASCADE? Thanks, Jean-Christian Imbeault
On Fri, 2003-09-19 at 14:02, Jean-Christian Imbeault wrote:
> Oliver Elphick wrote:
>
> >>Foreign Key constraints: $1 FOREIGN KEY (pid) REFERENCES products(id) ON
> >>UPDATE NO ACTION ON DELETE NO ACTION
> >
> > Drop the constraint; then add an amended one.
>
> Ok, how do I drop the constraint, it has no name.
From what you quoted above, its name is $1
ALTER TABLE xxx DROP CONSTRAINT "$1"; -- double quotes required since
-- $1 is an identifier
> Secondly what is the correct syntax for adding a new constraint with ON
> DELETE CASCADE?
ALTER TABLE xxx ADD CONSTRAINT "$1" FOREIGN KEY (pid) REFERENCES
products(id) ON UPDATE NO ACTION ON DELETE CASCADE;
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Bring ye all the tithes into the storehouse, that
there may be meat in mine house, and prove me now
herewith, saith the LORD of hosts, if I will not open
you the windows of heaven, and pour you out a
blessing, that there shall not be room enough to
receive it." Malachi 3:10
On Friday 19 September 2003 14:02, Jean-Christian Imbeault wrote: > Oliver Elphick wrote: > >>Foreign Key constraints: $1 FOREIGN KEY (pid) REFERENCES products(id) ON > >>UPDATE NO ACTION ON DELETE NO ACTION > > > > Drop the constraint; then add an amended one. > > Ok, how do I drop the constraint, it has no name. Its name is "$1" - use DROP CONSTRAINT "$1" ... > Secondly what is the correct syntax for adding a new constraint with ON > DELETE CASCADE? See the SQL command reference for ALTER TABLE. It's something like: ALTER TABLE mytable ADD CONSTRAINT constraint_name FOREIGN KEY ... etc -- Richard Huxton Archonet Ltd