Search code examples
sqlforeign-keyscascadealter-table

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

Solution

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