指定 SQL Server 刪除資料時連同關聯資料一併刪除或是清空(On Delete Cascade, ON DELETE SET NULL)

建立兩張欄位很少的範例資料表: 訂單和訂單明細

    
-- 訂單
CREATE TABLE Orders
(
    Id   INT PRIMARY KEY,
    Name NVARCHAR(100) not null
);

-- 訂單明細
CREATE TABLE OrderDetails
(
    Id          INT PRIMARY KEY,
    OrderId     INT
        constraint FK_OrderDetails_Orders_Id references Orders (Id),
    ProductName NVARCHAR(100),
);
    

新增幾筆範例資料:

    
INSERT INTO Orders (Id, Name) VALUES (1, N'訂單 1');
INSERT INTO Orders (Id, Name) VALUES (2, N'訂單 2');

INSERT INTO OrderDetails (Id, OrderId, ProductName) VALUES (1, 1, N'產品 A');
INSERT INTO OrderDetails (Id, OrderId, ProductName) VALUES (2, 1, N'產品 B');
INSERT INTO OrderDetails (Id, OrderId, ProductName) VALUES (3, 2, N'產品 C');
    

刪除 Id 為 1 的訂單時會出現下列錯誤:

    
-- delete Orders where Id = 1

[23000][547] Line 1: The DELETE statement conflicted with the REFERENCE constraint "FK_OrderDetails_Orders_Id".
The conflict occurred in database "my_database", table "dbo.OrderDetails", column 'OrderId'.
    

或是：

    
-- delete Orders where Id = 1

[23000][547] Line 1: DELETE 陳述式與 REFERENCE 條件約束 "FK_OrderDetails_Orders_Id" 衝突。衝突發生在資料庫 "my_database"，資料表 "dbo.OrderDetails", column 'OrderId'。
    

原因是因為 OrderDetails 資料表的 OrderId 欄位有綁定 Orders 的內容，所以只要我們想要刪除的資料會讓 OrderDetails 中的資料無法找到相對應的內容，就會無法刪除。

一併刪除

假設已經確定該筆資料不要了，要刪除時想要連同其他有關聯資料一併刪除該怎麼做？

先找到約束(constraint)名稱，以上面範例為例叫做 FK_OrderDetails_Orders_Id，先將此約束刪除:

    
ALTER TABLE OrderDetails DROP CONSTRAINT FK_OrderDetails_Orders_Id; -- 先刪除現有的外鍵約束
    

再重新建立約束，並加上 ON DELETE CASCADE

    
ALTER TABLE OrderDetails
    ADD CONSTRAINT FK_OrderDetails_Orders_Id
        FOREIGN KEY (OrderId) REFERENCES Orders (Id) ON DELETE CASCADE
    

再次執行刪除指令:

    
delete Orders where Id = 1
    

就可以成功刪除了，然後再查看 OrderDetails 資料表會發現 OrderId 為 1 的內容也一起被刪除了

原始內容:

    
訂單
+--+----+
|Id|Name|
+--+----+
|1 |訂單1|
|2 |訂單2|
+--+----+

訂單明細
+--+-------+-----------+
|Id|OrderId|ProductName|
+--+-------+-----------+
|1 |1      |產品 A      |
|2 |1      |產品 B      |
|3 |2      |產品 C      |
+--+-------+-----------+
    

刪除後:

    
訂單
+--+----+
|Id|Name|
+--+----+
|2 |訂單2|
+--+----+

訂單明細
+--+-------+-----------+
|Id|OrderId|ProductName|
+--+-------+-----------+
|3 |2      |產品 C      |
+--+-------+-----------+
    

清空欄位

清空和刪除的語法差不多，就只是把 ON DELETE CASCADE 改為 ON DELETE SET NULL 而已，只是有個限制，就是那個欄位要可以 NULL

    
ALTER TABLE OrderDetails
    ADD CONSTRAINT FK_OrderDetails_Orders_Id
        FOREIGN KEY (OrderId) REFERENCES Orders (Id) ON DELETE SET NULL