How to add "on delete cascade" constraints?
In PostgreSQL 8 is it possible to add ON DELETE CASCADES to the both foreign keys in the following table without dropping the latter?
# \d scores
Table "public.scores"
Column | Type | Modifiers
---------+-----------------------+-----------
id | character varying(32) |
gid | integer |
money | integer | not null
quit | boolean |
last_ip | inet |
Foreign-key constraints:
"scores_gid_fkey" FOREIGN KEY (gid) REFERENCES games(gid)
"scores_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
Both referenced tables are below - here:
# \d games
Table "public.games"
Column | Type | Modifiers
----------+-----------------------------+----------------------------------------------------------
gid | integer | not null default nextval('games_gid_seq'::regclass)
rounds | integer | not null
finished | timestamp without time zone | default now()
Indexes:
"games_pkey" PRIMARY KEY, btree (gid)
Referenced by:
TABLE "scores" CONSTRAINT "scores_gid_fkey" FOREIGN KEY (gid) REFERENCES games(gid)
And here:
# \d users
Table "public.users"
Column | Type | Modifiers
------------+-----------------------------+---------------
id | character varying(32) | not null
first_name | character varying(64) |
last_name | character varying(64) |
female | boolean |
avatar | character varying(128) |
city | character varying(64) |
login | timestamp without time zone | default now()
last_ip | inet |
logout | timestamp without time zone |
vip | timestamp without time zone |
mail | character varying(254) |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "cards" CONSTRAINT "cards_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "catch" CONSTRAINT "catch_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "chat" CONSTRAINT "chat_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "game" CONSTRAINT "game_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "hand" CONSTRAINT "hand_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "luck" CONSTRAINT "luck_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "match" CONSTRAINT "match_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "misere" CONSTRAINT "misere_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "money" CONSTRAINT "money_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "pass" CONSTRAINT "pass_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "payment" CONSTRAINT "payment_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "rep" CONSTRAINT "rep_author_fkey" FOREIGN KEY (author) REFERENCES users(id)
TABLE "rep" CONSTRAINT "rep_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "scores" CONSTRAINT "scores_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
TABLE "status" CONSTRAINT "status_id_fkey" FOREIGN KEY (id) REFERENCES users(id)
And also I wonder if it makes sense to add 2 index'es to the former table?
UPDATE: Thank you, and also I've got the advice at the mailing list, that I could manage it in 1 statement and thus without explicitly starting a transaction:
ALTER TABLE public.scores
DROP CONSTRAINT scores_gid_fkey,
ADD CONSTRAINT scores_gid_fkey
FOREIGN KEY (gid)
REFERENCES games(gid)
ON DELETE CASCADE;
Solution 1:
I'm pretty sure you can't simply add on delete cascade to an existing foreign key constraint. You have to drop the constraint first, then add the correct version. In standard SQL, I believe the easiest way to do this is to
- start a transaction,
- drop the foreign key,
- add a foreign key with
on delete cascade, and finally - commit the transaction
Repeat for each foreign key you want to change.
But PostgreSQL has a non-standard extension that lets you use multiple constraint clauses in a single SQL statement. For example
alter table public.scores
drop constraint scores_gid_fkey,
add constraint scores_gid_fkey
foreign key (gid)
references games(gid)
on delete cascade;
If you don't know the name of the foreign key constraint you want to drop, you can either look it up in pgAdminIII (just click the table name and look at the DDL, or expand the hierarchy until you see "Constraints"), or you can query the information schema.
select *
from information_schema.key_column_usage
where position_in_unique_constraint is not null
Solution 2:
Based off of @Mike Sherrill Cat Recall's answer, this is what worked for me:
ALTER TABLE "Children"
DROP CONSTRAINT "Children_parentId_fkey",
ADD CONSTRAINT "Children_parentId_fkey"
FOREIGN KEY ("parentId")
REFERENCES "Parent"(id)
ON DELETE CASCADE;
Solution 3:
Usage:
select replace_foreign_key('user_rates_posts', 'post_id', 'ON DELETE CASCADE');
Function:
CREATE OR REPLACE FUNCTION
replace_foreign_key(f_table VARCHAR, f_column VARCHAR, new_options VARCHAR)
RETURNS VARCHAR
AS $$
DECLARE constraint_name varchar;
DECLARE reftable varchar;
DECLARE refcolumn varchar;
BEGIN
SELECT tc.constraint_name, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY'
AND tc.table_name= f_table AND kcu.column_name= f_column
INTO constraint_name, reftable, refcolumn;
EXECUTE 'alter table ' || f_table || ' drop constraint ' || constraint_name ||
', ADD CONSTRAINT ' || constraint_name || ' FOREIGN KEY (' || f_column || ') ' ||
' REFERENCES ' || reftable || '(' || refcolumn || ') ' || new_options || ';';
RETURN 'Constraint replaced: ' || constraint_name || ' (' || f_table || '.' || f_column ||
' -> ' || reftable || '.' || refcolumn || '); New options: ' || new_options;
END;
$$ LANGUAGE plpgsql;
Be aware: this function won't copy attributes of initial foreign key. It only takes foreign table name / column name, drops current key and replaces with new one.
Solution 4:
Solution for multiple column constraints:
SELECT
'ALTER TABLE myschema.' || cl.relname ||
' DROP CONSTRAINT ' || con.conname || ',' ||
' ADD CONSTRAINT ' || con.conname || ' ' || pg_get_constraintdef(con.oid) || ' ON DELETE CASCADE;'
FROM pg_constraint con, pg_class cl
WHERE con.contype = 'f' AND con.connamespace = 'myschema'::regnamespace::oid AND con.conrelid = cl.oid