代码之家 › 专栏 › 技术社区 › Miguel Ping

删除具有非空FK约束的MxN关系中的所有条目

ansi-sql many-to-many sql

0

Miguel Ping · 技术社区 · 16 年前

我有两个实体A和B,它们通过中间表在MxN关系中相互关联,因此总共有三个表。我的关系表R对其他两个表具有不可为空的FK约束。

是否可以在不诉诸存储过程和不违反非空约束的情况下从三个表中删除条目?

我想要像这样的东西:

delete from A a, B b, R r where a.id=r.fk_a and B.id=r.fk_B a

2 回复 | 直到 11 年前

1

2

Roland Bouman 16 年前

视情况而定。如果r和b之间的fk指定为 ON DELETE CASCADE

START TRANSACTION;

DELETE FROM b
WHERE  id IN (
    SELECT r.b_id
    FROM       r
    INNER JOIN a
    ON         r.a_id = a.id
    WHERE      <some condition on a>
);

DELETE FROM a
WHERE      <some condition on a>;

COMMIT WORK;

如果没有级联删除,则可以使用临时表执行此操作:

CREATE TEMPORARY TABLE to_be_deleted_from_b 
LIKE b;

START TRANSACTION;

INSERT INTO to_be_deleted_from_b
SELECT * 
FROM b
INNER JOIN r
ON         b.id = r.b_id
INNER JOIN a
ON         r.a_id = a.id
WHERE      <some condition on a>;

DELETE FROM r
WHERE  a_id IN (
    SELECT a.id
    FROM   a
    WHERE  <some condition on a>
);

DELETE FROM a
WHERE  <some condition on a>;

DELETE FROM b
WHERE b.id IN (
    SELECT id
    FROM   to_be_deleted_from_b
);

COMMIT WORK;

DROP TABLE to_be_deleted_from_b

2

1

Lieven Keersmaekers 16 年前

您可以通过三次删除和使用临时表来实现这一点。

首先,将所有要删除的记录添加到临时表中。
其次,从中间表中删除所有符合约束的关系。
第三,从中间表中删除所有不存在的记录。
最后,从B中删除中间表中不存在的所有记录。

例子

BEGIN TRAN

INSERT INTO #R
SELECT R.*
FROM R r
     INNER JOIN A a ON a.ID = r.fk_a
WHERE a.Column = 'AConstraint'

DELETE FROM R
FROM R r
     INNER JOIN A a ON a.ID = r.fk_a
WHERE a.Column = 'AConstraint'

DELETE FROM A
FROM A a
     INNER JOIN #R r ON r.fk_a = a.ID

DELETE FROM B
FROM B b
     INNER JOIN #R r ON r.fk_b = b.ID
WHERE r.ID IS NULL

DROP TABLE #R

COMMIT TRAN