Oracle to SQL Server Migration issue - On delete cascade

Question

Folks,

I am working on Oracle to MS SQL Server migration. One of the issue I came across is that "ON DELETE CASCADE" works fine in Oracle, but not in SQL Server. Is there any alternative to the syntax/command or is it something we need to manually remove/fix it.

Could not get a valid link/reference on web, hence requesting here. If this is already answered, please help re-directing to the appropriate page.

Answer 1

As far as I know, ON DELETE CASCADE is supported in SQL Server too. Following is the syntax:

• Create table

    CREATE TABLE child_table
    (
      column1 datatype [ NULL | NOT NULL ],
      column2 datatype [ NULL | NOT NULL ],
      ...

      CONSTRAINT fk_name
        FOREIGN KEY (child_col1, child_col2, ... child_col_n)
        REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n)
        ON DELETE CASCADE
    );

• Alter table

    ALTER TABLE child_table
    ADD CONSTRAINT fk_name
        FOREIGN KEY (child_col1, child_col2, ... child_col_n)
        REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n)
        ON DELETE CASCADE;

See an example here.