How to REMOVE an "on delete cascade"?

Phoenix Kiula wrote:

Hi. Hard to find this command in the documentation - how should I alter a table to REMOVE the "on
delete cascade" constraint from a table? Thanks.

Unless you want to mess with the catalogs directly, I believe that
you have to create a new constraint and delete the old one, like:

Table "laurenz.b"
Column | Type | Modifiers
--------+---------+-----------
b_id | integer | not null
a_id | integer | not null
Indexes:
"b_pkey" PRIMARY KEY, btree (b_id)
"b_a_id_ind" btree (a_id)
Foreign-key constraints:
"b_a_id_fkey" FOREIGN KEY (a_id) REFERENCES a(a_id) ON DELETE CASCADE

ALTER TABLE b ADD CONSTRAINT scratch FOREIGN KEY (a_id) REFERENCES a(a_id);

ALTER TABLE b DROP CONSTRAINT b_a_id_fkey;

ALTER TABLE b RENAME CONSTRAINT scratch TO b_a_id_fkey;

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

You can do all that in a single sql command.

ALTER TABLE b DROP CONSTRAINT b_a_id_fkey, ADD CONSTRAINT b_a_id FOREIGN
KEY (a_id) REFERENCES a(a_id);

On Fri, Jun 28, 2013 at 5:55 AM, Albe Laurenz <laurenz.albe@wien.gv.at>wrote:

Phoenix Kiula wrote:

Hi. Hard to find this command in the documentation - how should I alter

a table to REMOVE the "on

delete cascade" constraint from a table? Thanks.

Unless you want to mess with the catalogs directly, I believe that
you have to create a new constraint and delete the old one, like:

Table "laurenz.b"
Column | Type | Modifiers
--------+---------+-----------
b_id | integer | not null
a_id | integer | not null
Indexes:
"b_pkey" PRIMARY KEY, btree (b_id)
"b_a_id_ind" btree (a_id)
Foreign-key constraints:
"b_a_id_fkey" FOREIGN KEY (a_id) REFERENCES a(a_id) ON DELETE CASCADE

ALTER TABLE b ADD CONSTRAINT scratch FOREIGN KEY (a_id) REFERENCES a(a_id);

ALTER TABLE b DROP CONSTRAINT b_a_id_fkey;

ALTER TABLE b RENAME CONSTRAINT scratch TO b_a_id_fkey;

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Regards,
Richard Broersma Jr.