Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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; 
like image 202
Alexander Farber Avatar asked Apr 27 '12 19:04

Alexander Farber


People also ask

Can I add on delete cascade to existing foreign key?

1 Answer. You can not add ON DELETE CASCADE to an already existing constraint. You should drop and re-create the constraint.

How do you add cascade delete to existing FK constraint in Postgres?

If you want to add an on delete cascade to an existing foreign key constraint, you are going to need two statements. The first statement will drop the constraint and the second statement will recreate it with the addition of the on delete clause.

Is on delete cascade a constraint?

ON DELETE CASCADE constraint is used in MySQL to delete the rows from the child table automatically, when the rows from the parent table are deleted. For example when a student registers in an online learning platform, then all the details of the student are recorded with their unique number/id.

What is Onton delete Cascade constraint in MySQL?

ON DELETE CASCADE constraint is used in MySQL to delete the rows from the child table automatically, when the rows from the parent table are deleted. For example when a student registers in an online learning platform, then all the details of the student are recorded with their unique number/id.

How to add delete cascade to an existing FOREIGN KEY constraint?

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 Repeat for each foreign key you want to change.

How does the on delete Cascade clause work?

For this foreign key, we have specified the ON DELETE CASCADE clause which tells SQL Server to delete the corresponding records in the child table when the data in the parent table is deleted. So in this example, if a product_id value is deleted from the products table, the corresponding records in the inventory table...

What is MySQL on delete Cascade?

However, MySQL provides a more effective way called ON DELETE CASCADE referential action for a foreign key that allows you to delete data from child tables automatically when you delete the data from the parent table. MySQL ON DELETE CASCADE example. Let’s take a look at an example of using MySQL ON DELETE CASCADE.


2 Answers

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 
like image 84
Mike Sherrill 'Cat Recall' Avatar answered Oct 05 '22 22:10

Mike Sherrill 'Cat Recall'


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; 
like image 45
Fellow Stranger Avatar answered Oct 05 '22 22:10

Fellow Stranger