1

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.

computingfreak
  • 4,939
  • 1
  • 34
  • 51
Roopesh Majeti
  • 556
  • 1
  • 11
  • 23
  • 2
    I'm not a SQL Server expert, but I'm pretty sure there is no such error message as "*does not work*" in SQL Server. Show us your code and the exact error message you get. –  Jun 08 '15 at 09:10
  • I think [this](http://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server) Answer will be helpful to you. – sohal Jun 08 '15 at 09:25

1 Answers1

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.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • I got it now.. Thanks. But another issue came up. I need to use a circular reference to the same table, but different column. Is it something that can be in sql server or move the respective logic to application code ? – Roopesh Majeti Jun 08 '15 at 12:06
  • @RoopeshMajeti Please ask a new question for that, and next time please post some sample data and what you tried. Please mark it as answered, would help others. – Lalit Kumar B Jun 08 '15 at 12:13