代码之家 › 专栏 › 技术社区 › Ashish Gupta Shiva

Sql server 2005-在所有相关表中级联删除

sql-server-2005

1

Ashish Gupta Shiva · 技术社区 · 15 年前

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)

)

外键:-

/****** 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

--样本数据

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')

我想下面会删除所有表中的所有数据:-

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

相反,我不得不写下:-

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

有什么方法可以删除单个delete语句中的所有数据吗?

4 回复 | 直到 15 年前

1

0

Quassnoi 15 年前

不能从中删除记录 A 和 C 使用单个语句,因为此表不在可传递的引用关系中。

如果要从中删除所有记录 一个 和有相应记录的 B

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

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

2

1

Jason 15 年前

基本上,如果我理解正确,您只能从“b”中删除数据,它将删除“a”和“c”中的数据。

A<--B[删除]--gt;C

从 Microsoft website :

关于删除级联

指定如果试图删除其他表中现有行中外键引用了键的行,则包含这些外键的所有行也将被删除。如果在目标表上还定义了级联引用操作,则还将对从这些表中删除的行执行指定的级联操作。

3

1

pavanred 15 年前

所以在您的例子中,表B是表A和表C的子表。

类似地,从表C中删除将从表B中删除其相应的子行。

编辑-

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

4

0

AndyD273 15 年前

嗯,可以改变约束条件吗?

我所看到的问题是C有一个1对多的a,B有一个1对多的C,所以与其让AId是B中的外键,不如让AId是C中的外键, 如果从C中删除一个项,它将级联到B,而不是A,如果从B中删除一个项,它将不会影响其他两个表中的任何一个。

我的建议是:

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

有了这个设置, Delete From A 会一次把三张桌子都清理干净。