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