当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - CASCADE behaviour on the drop of a foreign key

问题描述:

I am not clear about what happens when a "foreign key constraint" is deleted specifying the option CASCADE.

For instance, consider this command

ALTER TABLE table1 DROP CONSTRAINT foreignKeyToTable2 CASCADE.

What the option CASCADE is supposed to do in this case? What would happen if I omitted it? And if I wrote RESTRICT instead of CASCADE?

Note: this example of query is excerpted from "Ramez Elmasri, Shamkant B. Navathe - Fundamentals of database systems, end of chapter 5".

网友答案:

The cascade option to drop a constraint is only needed when dropping primary keys, not when dropping a foreign key.

Consider this example in Postgres:

create table t1 (id integer, constraint pk_one primary key (id));
create table t2 (id integer primary key, id1 integer references t1);

When you try to run:

alter table t1 drop constraint pk_one;

You get:

ERROR: cannot drop constraint pk_one on table t1 because other objects depend on it
  Detail: constraint t2_id1_fkey on table t2 depends on index pk_one
  Hint: Use DROP ... CASCADE to drop the dependent objects too. 

If you run:

alter table t1 drop constraint pk_one cascade;

you get:

NOTICE:  drop cascades to constraint t2_id1_fkey on table t2

Telling you that the foreign key that needed the primary key was dropped as well.


Note that not all DBMS support a cascading drop. Postgres and Oracle do.
MySQL, SQL Server or Firebird do not. You need to drop the foreign keys manually in those DBMS.

分享给朋友:
您可能感兴趣的文章:
随机阅读: