How to add "on delete cascade" constraints?

Question

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;

Albumin

A little OT, but I notice that you have not created indexes on referencing columns (for example, pref_scores.gid). Deletes on the referenced table will take a long time without those, if you get many rows in those tables. Some databases automatically create an index on the referencing column(s); PostgreSQL leaves that up to you, since there are some cases where it isn't worthwhile. – Pamplona

Thank you! I actually noticed that delete's take long, but didn't know that's the reason – Albumin

Which cases would that be, when indexes on foreign keys aren't worthwhile? – Albumin

@AlexanderFarber: Good reminder about PostgreSQL allowing multiple constraint clauses in a single statement. Since I have to support multiple platforms, I tend to rely on standard SQL. I often forget about these useful extensions. – Midland

I incorporated your finding into my answer. (That single statement is also a single transaction.) – Midland

@AlexanderFarber: When might you want to omit an index on the referencing column(s) of a FK? When there is another index not an exact match which will work well enough (e.g., you might have a trigram index for frequent similarity searches which will be OK for FK delete, too). When deletes are infrequent and can be scheduled off-hours. When a table has frequent updates of the referencing value. When the referencing table is very small but frequently updated. Exceptions occur often enough that the PostgreSQL community prefers to have control over it rather than making it automatic. – Pamplona

Answer 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

Hernandez

Thanks, that is what I thought too - but what to do with FOREIGN KEYs? Are they just constraints (similar to NOT NULL) which can be dropped and readded easily? – Albumin

@AlexanderFarber: Yes, they're named constraints you can drop and add easily. But you probably want to do that within a transaction. Updated my answer with more detail. – Midland

+1 for looking ot up in pgAdminIII. It even gives you the DROP CONSTRAINT and ADD CONSTRAINT commands, so you can just copy and paste into a query window and edit the command to what you want. – Onia

After writing up the query, I noticed my Postgres GUI (Navicat) let's me trivially do this change from within the GUI: dl.dropboxusercontent.com/spa/quq37nq1583x0lf/wwqne-lw.png – Credits

For large tables, is this possible with NOT VALID and validating in a separate transaction? I have an unanswered question about this. – Principalities

@TheCloudlessSky: PostgreSQL supports declaring foreign key constraints as not valid in one transaction, and validating them with alter table table_name validate constraint constraint_name; in another transaction. You can test that on small tables. – Midland

will this cause rebuilding index for foreign key? – Carma

also, will this operation lock the table? – Carma

@Julia: PostgreSQL doesn't automatically create an index on a foreign key's referencing columns. Docs. So dropping a foreign key constraint won't affect any index you've created on the referencing columns. Dropping a constraint requires an ACCESS EXCLUSIVE table lock. I think adding a constraint also requires an ACCESS EXCLUSIVE table lock, but there are ways to declare a foreign key constraint that commits without having to check existing data for violations. – Midland

Answer 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;

Eddieeddina

Just FYI to the next guy, in postgres you don't have to "manually" name your constraints like this. So instead of ADD CONSTRAINT "Children_parentId_fkey" FOREIGN KEY ("parentId") REFERENCES "Parent"(id) you can just do ADD FOREIGN KEY ("parentId") REFERENCES "Parent"(id) etc and postgres will automatically (appropriately) name your constraint "ChildrenId_parentId_fkey" for you. – Antheridium

Answer 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.

Incompletion

Is it possible to create this function as a trigger and to just run this function before delete? – Lanctot

I guess this should be possible, tho not sure it's a good idea ¿? What would be the use-case for this? – Barger

Answer 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

Intermarriage

I had to edit the output of this a little bit, but this is a good start. The pg_get_constraintdef sometimes will also output 'set not null' and 'on delete XX' and this will not go well with the second 'on delete cascade' – Lauree

Answer 5

Solution for changing options on many tables.

Usage:

SELECT replace_foreign_key('user_rates_posts', 'post_id', 'ON DELETE CASCADE');

Code:

CREATE OR REPLACE FUNCTION replace_foreign_key(f_table VARCHAR, f_column VARCHAR, new_options VARCHAR)
RETURNS SETOF VARCHAR AS $$
  DECLARE reftable varchar;
  DECLARE refcolumn varchar;
  DECLARE constraint_name_tmp varchar;
BEGIN

FOR reftable, refcolumn, constraint_name_tmp IN
    SELECT kcu.table_name AS table_name, kcu.column_name AS column_name, tc.constraint_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 ccu.table_name= f_table AND ccu.column_name= f_column
    LOOP

        EXECUTE 'ALTER TABLE ' || reftable || ' DROP CONSTRAINT ' || constraint_name_tmp ||
                ', ADD CONSTRAINT ' || constraint_name_tmp || ' FOREIGN KEY (' || refcolumn || ') ' ||
                ' REFERENCES ' || f_table || '(' || f_column || ') ' || new_options || ';';

        RETURN NEXT 'Constraint replaced: ' || constraint_name_tmp || ' (' || f_table || '.' || f_column ||
                    ' -> ' || reftable || '.' || refcolumn || '); New options: ' || new_options;
    END LOOP; 
END; $$ LANGUAGE plpgsql;

thanks Daniel, for your code

Quoth