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;
1 Answer. You can not add ON DELETE CASCADE to an already existing constraint. You should drop and re-create the constraint.
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.
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.
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.
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.
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...
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.
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
on delete cascade
, and finallyRepeat 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
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With