adding on delete cascade constraint?

[Jean-Christian Imbeault]

Currently I have a table defined as such:

TAL=# \d internal_keywor ds
Table "public.interna l_keywords"
Column | Type | Modifiers
---------+---------+-----------
keyword | text | not null
pid | integer | not null
Indexes: internal_keywor ds_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


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Oliver Elphick]

Re: adding on delete cascade constraint?

On Fri, 2003-09-19 at 11:09, Jean-Christian Imbeault wrote:[color=blue]
> Currently I have a table defined as such:
>
> TAL=# \d internal_keywor ds
> Table "public.interna l_keywords"
> Column | Type | Modifiers
> ---------+---------+-----------
> keyword | text | not null
> pid | integer | not null
> Indexes: internal_keywor ds_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.[/color]

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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Jean-Christian Imbeault]

Re: adding on delete cascade constraint?

Oliver Elphick wrote:
[color=blue][color=green]
>>Foreign Key constraints: $1 FOREIGN KEY (pid) REFERENCES products(id) ON
>>UPDATE NO ACTION ON DELETE NO ACTION[/color]
>
> Drop the constraint; then add an amended one.[/color]

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



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Oliver Elphick]

Re: adding on delete cascade constraint?

On Fri, 2003-09-19 at 14:02, Jean-Christian Imbeault wrote:[color=blue]
> Oliver Elphick wrote:
>[color=green][color=darkred]
> >>Foreign Key constraints: $1 FOREIGN KEY (pid) REFERENCES products(id) ON
> >>UPDATE NO ACTION ON DELETE NO ACTION[/color]
> >
> > Drop the constraint; then add an amended one.[/color]
>
> Ok, how do I drop the constraint, it has no name.[/color]
[color=blue]
>From what you quoted above, its name is $1[/color]

ALTER TABLE xxx DROP CONSTRAINT "$1"; -- double quotes required since
-- $1 is an identifier
[color=blue]
> Secondly what is the correct syntax for adding a new constraint with ON
> DELETE CASCADE?[/color]

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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Richard Huxton]

Re: adding on delete cascade constraint?

On Friday 19 September 2003 14:02, Jean-Christian Imbeault wrote:[color=blue]
> Oliver Elphick wrote:[color=green][color=darkred]
> >>Foreign Key constraints: $1 FOREIGN KEY (pid) REFERENCES products(id) ON
> >>UPDATE NO ACTION ON DELETE NO ACTION[/color]
> >
> > Drop the constraint; then add an amended one.[/color]
>
> Ok, how do I drop the constraint, it has no name.[/color]

Its name is "$1" - use

DROP CONSTRAINT "$1" ...
[color=blue]
> Secondly what is the correct syntax for adding a new constraint with ON
> DELETE CASCADE?[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org