locked
on delete cascade RRS feed

  • Question

  • Hi everybody

    Can anyone show me how can i set up "on delete cascade" constraint on slq server 2010 between 2 tables???

    thnx in advanced

     

    Thursday, August 19, 2010 9:10 PM

Answers

  • You can mention it while defining relationship in Design View.

    There is an expandable option 'INSERT and UPDATE Specification'.

    On expanding it, you will see Delete Rule and Update Rule.

    Here you can set CASCADE, one of the option in the drop down list.

     

    • Marked as answer by aldi007 Friday, August 20, 2010 8:26 PM
    Friday, August 20, 2010 6:38 AM

All replies

  • By using cascading referential integrity constraints, you can define the actions that the SQL Server takes when a user tries to delete or update a key to which existing foreign keys point.

    ON DELETE CASCADE
    Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows that contain those foreign keys are also deleted.

    Example :

    The following example is used to create the Books and the Authors tables and create a foreign key constraint which will perform the cascade delete action, therefore, when a row in the Authors table is deleted, the corresponding rows in the Books are also deleted:

    CREATE TABLE Books (
      BookID INT NOT NULL PRIMARY KEY,
      AuthorID INT NOT NULL,
      BookName VARCHAR(100) NOT NULL,
      Price MONEY NOT NULL
    )
    GO
    
    CREATE TABLE Authors (
      AuthorID INT NOT NULL PRIMARY KEY,
      Name VARCHAR(100) NOT NULL
    )
    GO
    
    ALTER TABLE Books 
    ADD CONSTRAINT fk_author 
    FOREIGN KEY (AuthorID) 
    REFERENCES Authors (AuthorID) ON DELETE CASCADE 
    GO
    
    <br/>
    
    Source:  http://www.mssqlcity.com/Articles/General/using_constraints.htm

    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    Friday, August 20, 2010 4:49 AM
  • Hi,

    Latest Sql server version available for use is SQL server 2008 R2 not SQL 2010.

    The below example might help in understing further.

    Create table Employee
    ( Id int identity(1,1) primary key,
    Name nvarchar(100))
    go
    Create table EmployeeDetail
    ( Id int,
    DateOFBirth datetime,
    JoinDate datetime)
    go
    Alter table EmployeeDetail add constraint FK_EmployeeDetail_Employee Foreign Key(Id) 
    REFERENCES Employee (ID) on DELETE CASCADE
    
    Go
    Insert into Employee (Name)
    values
    ('Test1'),
    ('Test2'),
    ('Test3')
    Go
    
    Insert into EmployeeDetail (Id,DateOFBirth,JoinDate)
    select 1,DATEADD(yy,-25,getdate()),GETDATE()
    union all
    select 2,DATEADD(yy,-28,getdate()),GETDATE()
    union all
    select 3,DATEADD(yy,-30,getdate()),GETDATE()
    GO
    
    select * from Employee where ID = 2
    select * from EmployeeDetail where ID = 2
    
    go
    

    Now, when you delete it from employee table, rows gets deleted from employeeDetail table automatically.

    delete from Employee where id = 2
    select * from EmployeeDetail where ID = 2
    
    

    If while creating a foreign key if delete cascade is not specified we would have got following error in case we delete from Employee table.

    "The DELETE statement conflicted with the REFERENCE constraint"

     


    -Chintak
    • Proposed as answer by RohitDBA Friday, August 20, 2010 6:29 AM
    Friday, August 20, 2010 4:55 AM
  • You can mention it while defining relationship in Design View.

    There is an expandable option 'INSERT and UPDATE Specification'.

    On expanding it, you will see Delete Rule and Update Rule.

    Here you can set CASCADE, one of the option in the drop down list.

     

    • Marked as answer by aldi007 Friday, August 20, 2010 8:26 PM
    Friday, August 20, 2010 6:38 AM
  • You can mention it while defining relationship in Design View.

    There is an expandable option 'INSERT and UPDATE Specification'.

    On expanding it, you will see Delete Rule and Update Rule.

    Here you can set CASCADE, one of the option in the drop down list.

     

     


    Thnx so much guys.I was looking for something on design view and i got it. really thnx
    Friday, August 20, 2010 8:28 PM