级联删除，，，，给个答案吧？

[BinjinyLong]

我有五张表，a,b,c,d,e,每一张表都是前一个引用后一个的id （primary key）d,e是设定了级联删除，现在我要在删除c的时候在删除下面的，他报错因为d的级联删除有问题

[ai_li7758521]

关注

[nalnait]

先看看哪个表和这表有主外键关系，先删除其中的记录

[ChinaJiaBing]

create table ta(id int not null)
create table tb(id int , aid int)
insert into ta values(1)
insert into ta values(2)
insert into tb values(1 , 1)
insert into tb values(2 , 2)
insert into tb values(3 , 1)
go

--一、查看原始数据
--ta表的原始数据
select * from ta
/*
id          
----------- 
1
2
*/
--tb表的原始数据
select * from tb
/*
id          aid         
----------- ----------- 
1           1
2           2
3           1
*/

--二、看看没有创建级联删除时的情况(删除ta表id=1的数据,看看是否影响tb表)
delete from ta where id = 1
select * from ta
/*
id          
----------- 
2
*/
select * from tb
/*
id          aid         
----------- ----------- 
1           1
2           2
3           1
*/

--三、恢复原始数据，创建级联删除,删除ta表id=1的数据,看看是否影响tb表
insert into ta values(1)
--为ta创建主健
alter table ta add constraint pk_ta_id primary key (id)
go
--为tb创建外健，并指定级联删除
alter table tb add constraint fk_tb_aid foreign key (aid) references ta(id) on delete cascade
go
delete from ta where id = 1
select * from ta
/*
id          
----------- 
2
*/
select * from tb
/*
id          aid         
----------- ----------- 
2           2
*/

--删除级联约束
alter table tb drop constraint fk_tb_aid
go
--删除测试表
drop table ta , tb
go

[liping_ycit]

可以直接在创建表的时候设置外键约束以及级联删除

CREATE TABLE [dbo].[a](
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [name] [varchar](10) NULL)
GO
CREATE TABLE [dbo].[b](
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [AID] [int] NULL references a(id) ON DELETE CASCADE,
    [name] [varchar](10) NULL)

GO
CREATE TABLE [dbo].[c](
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [BID] [int] NULL references b(id) ON DELETE CASCADE,
    [name] [varchar](10) NULL)
GO
CREATE TABLE [dbo].[d](
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [CID] [int] NULL references c(id) ON DELETE CASCADE,
    [name] [varchar](10) NULL)
GO
CREATE TABLE [dbo].[e](
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [DID] [int] NULL references d(id) ON DELETE CASCADE,
    [name] [varchar](10) NULL)
GO

[Tomzzu]

是后一个引用前一个的ID吧
这样的话, 删除C 先删除E,D才行

[jia_guijun]

--创建表
CREATE TABLE [dbo].[a](
	[id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[name] [varchar](10) NULL)
GO
CREATE TABLE [dbo].[b](
	[id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[AID] [int] NULL,
	[name] [varchar](10) NULL)

GO
CREATE TABLE [dbo].[c](
	[id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[BID] [int] NULL,
	[name] [varchar](10) NULL)
GO
CREATE TABLE [dbo].[d](
	[id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[CID] [int] NULL,
	[name] [varchar](10) NULL)
GO
CREATE TABLE [dbo].[e](
	[id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[DID] [int] NULL,
	[name] [varchar](10) NULL)
GO
--设置关系
ALTER TABLE [dbo].[b]  WITH CHECK ADD  CONSTRAINT [FK_b_a] FOREIGN KEY([AID])
REFERENCES [dbo].[a] ([id])
ON DELETE CASCADE  --删除规则设置为“层叠”
GO
ALTER TABLE [dbo].[b] CHECK CONSTRAINT [FK_b_a]
GO
ALTER TABLE [dbo].[c]  WITH CHECK ADD  CONSTRAINT [FK_c_b] FOREIGN KEY([BID])
REFERENCES [dbo].[b] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[c] CHECK CONSTRAINT [FK_c_b]
GO
ALTER TABLE [dbo].[d]  WITH CHECK ADD  CONSTRAINT [FK_d_c] FOREIGN KEY([CID])
REFERENCES [dbo].[c] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[d] CHECK CONSTRAINT [FK_d_c]
GO
ALTER TABLE [dbo].[e]  WITH CHECK ADD  CONSTRAINT [FK_e_d] FOREIGN KEY([DID])
REFERENCES [dbo].[d] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[e] CHECK CONSTRAINT [FK_e_d]
GO
--加入测试数据
insert a(name)	values('a1')
insert a(name)	values('a2')
insert a(name)	values('a3')
insert a(name)	values('a4')

insert b(aid,name)	values(1,'a1b')
insert b(aid,name)	values(2,'a2b')
insert b(aid,name)	values(3,'a3b')
insert b(aid,name)	values(1,'a1b')
select * from b

insert c(bid,name)	values(1,'a1b1c')
insert c(bid,name)	values(2,'a2b2c')
insert c(bid,name)	values(3,'a3b3c')
insert c(bid,name)	values(4,'c')
select * from c

--删除a表中的一条记录，可以看到其他表中与此记录有关的全部被删除了
delete a where id=2
select * from a
select * from b
select * from c

[yingzhilian2008]

先删除E 然后会级联删除D 才可以删除掉c

[BinjinyLong]

O(∩_∩)O谢谢！！