Sql server 2005 - Cascade delete in all related tables

Question

CREATE Table A
(
 AId int ,
 AName varchar(100)
)

CREATE Table B
(
 BId int,
 AId int,
 CId int,
 BName varchar(100)
)

CREATE Table C
(
 CId int,
 CName varchar (100)

)

"A" has foreign key in "B" and "C" also has foreign key in B. Both foreign keys have Cascade delete enabled.

Foreign key :-

/****** Object:  ForeignKey [FK_B_A]    Script Date: 10/28/2010 17:20:16 ******/
ALTER TABLE [dbo].[B]  WITH CHECK ADD  CONSTRAINT [FK_B_A] FOREIGN KEY([AId])
REFERENCES [dbo].[A] ([AId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[B] CHECK CONSTRAINT [FK_B_A]
GO
/****** Object:  ForeignKey [FK_B_C]    Script Date: 10/28/2010 17:20:16 ******/
ALTER TABLE [dbo].[B]  WITH CHECK ADD  CONSTRAINT [FK_B_C] FOREIGN KEY([CId])
REFERENCES [dbo].[C] ([CId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[B] CHECK CONSTRAINT [FK_B_C]
GO

-- Sample data

INSERT INTO A
VALUES (1, 'Ashish')
INSERT INTO A
VALUES (2, 'Sanjay')
INSERT INTO A
VALUES (3, 'Vivek')

INSERT INTO B
VALUES
(1,1,10,'Ashish1')
INSERT INTO B
VALUES
(2,1,11,'Ashish2')
INSERT INTO B
VALUES
(3,1,12,'Ashish3')


INSERT INTO B
VALUES
(4,2,13,'Ashish1')
INSERT INTO B
VALUES
(5,2,14,'Sanjay')
INSERT INTO B
VALUES
(6,3,15,'Vivek')

INSERT INTO C
VALUES
(10, 'Ashish Data1')
INSERT INTO C
VALUES
(11, 'Ashish Data2')

INSERT INTO C
VALUES
(12, 'Ashish Data3')

INSERT INTO C
VALUES
(13, 'Ashish Data4')

INSERT INTO C
VALUES
(14, 'sanjay Data1')

INSERT INTO C
VALUES
(15, 'Vivek Data1')

I thought following would delete all data from all the tables:-

DELETE a FROM A a
INNER JOIN B ON A.AId = B.AId
INNER JOIN C ON B.CId = C.CID

instead I had to write this:-

DELETE a FROM A a
INNER JOIN B ON A.AId = B.AId

DELETE b FROM B b
INNER JOIN C ON B.CId = C.CID

Is there any way to delete all data in s single delete statement?

Answer 1

Basically, if I understood correctly, you can only delete the data from 'b' and it will delete the data in 'a' and 'c'.

A <-- B [delete] --> C

From the Microsoft website:

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 containing those foreign keys are also deleted. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the rows deleted from those tables.

Answer 2

Cascading delete would mean that when a delete is performed on a parent table then the corresponding rows in child tables which have references to the parent table are also deleted.

So in your case table B is the child table for both table A and table C.

So, you cannot have one delete statement to remove data from all the tables. Instead, you can delete a row from table A and it will remove corresponding child rows from table B.

Similarly, a delete from table C will remove its corresponding child rows from table B.

Edit - THe queries that you have written are deletes without a where clause. So, if you are trying to delete all the rows from table A and table C and there by deleting all referenced rows in table B, you can use this -

DELETE FROM A --deletes all rows in A and corresponding referenced rows in B

DELETE FROM C --deletes all rows in C and corresponding referenced rows in B

Answer 3

Hmm, is it possible to change the constraints?

The problem as I see it is that C has a 1 to many with A, and B has a 1 to many with C, so instead of having AId be a foreign key in B, have AId be the foreign key in C, So if you delete a person from A, it'll cascade to C, which will cascade to B. If you delete an item from C, it'll cascade to B, but not A, and if you delete an item from B it wont effect either of the other two tables.

My suggestion:

CREATE TABLE [A] (
    [AId] [int] NOT NULL ,
    [AName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_A] PRIMARY KEY  CLUSTERED 
    (
        [AId]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GO

CREATE TABLE [C] (
    [CId] [int] NOT NULL ,
    [CName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [AId] [int] NULL ,
    CONSTRAINT [PK_C] PRIMARY KEY  CLUSTERED 
    (
        [CId]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_C_A] FOREIGN KEY 
    (
        [AId]
    ) REFERENCES [A] (
        [AId]
    ) ON DELETE CASCADE 
) ON [PRIMARY]
GO

CREATE TABLE [B] (
    [BId] [int] NOT NULL ,
    [AId] [int] NULL ,
    [CId] [int] NULL ,
    [BName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_B] PRIMARY KEY  CLUSTERED 
    (
        [BId]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_B_C] FOREIGN KEY 
    (
        [CId]
    ) REFERENCES [C] (
        [CId]
    ) ON DELETE CASCADE  NOT FOR REPLICATION 
) ON [PRIMARY]
GO

With this setup, Delete From A will clean out all 3 tables in one go.

Edit: you can keep AId in B to make joins easier, but it won't have any thing to do with the delete cascade.

Answer 4

You cannot delete records from A and C with a single statement because this tables are not in a transitive reference relationship.

If you want to delete all records from A and C that have corresponding records in B, you should use two statements:

DELETE  a
FROM    a
JOIN    b
ON      b.aid = a.aid

DELETE  c
FROM    c
JOIN    b
ON      b.cid = c.cid