Oracle drop constraint cascade equivalent in Sql S

Question

In Oracle, to drop the constraint PK_SAI I use the syntax:

ALTER TABLE "SAISIE" 
    DROP CONSTRAINT "PK_SAI" CASCADE;

What is the equivalent of this in SQL Server?

Answer 1

You are thinking of the CASCADE feature on FOREIGN KEY constraints, in relation to actual DELETE statements.

ALTER TABLE t2 add constraint FK_T2 foreign key(t_id) references t(id)
   ON DELETE CASCADE;

Dropping a constraint with CASCADE does not delete any rows.

DELETE deletes rows, if you have enabled ON DELETE CASCADE.

Dropping the constraint simply drops the constraint (and associated indexes and dependent constraints), not data rows. In SQL Server ALTER TABLE ... I am not aware that there is a "CASCADE" option as in Oracle.

From Oracle docs http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3001.htm#i2103845 for the ALTER TABLE statement:

CASCADE Specify CASCADE if you want all other integrity constraints that depend on the dropped integrity constraint to be dropped as well.