// SQL Server 錯誤參考

SQL Server 錯誤 547:陳述式與條件約束衝突

INSERT、UPDATE 或 DELETE 違反了 FOREIGN KEY 或 CHECK 約束:子資料列沒有父資料列、父資料列還有子資料列,或檢查失敗。

錯誤號碼
547
嚴重性
16
類別
條件約束與索引鍵
錯誤訊息
The %ls statement conflicted with the %ls constraint "%.*ls". The conflict occurred in database "%.*ls", table "%.*ls"%ls%.*ls%ls.

它的意義

錯誤 547 涵蓋兩種條件約束。對於一個 FOREIGN KEY,代表這條陳述式會留下指向空處的參照:插入或更新父資料不存在的子資料列,或刪除、改動仍有子資料列引用的父資料。有了 CHECK 約束,表示某個資料列未通過該約束的條件。訊息會指出陳述式類型、約束,以及涉及的資料庫、資料表與欄位。

常見原因

  • 插入的子列帶有外鍵值,卻沒有對應的父列(ID 錯誤,或父列尚未插入)。
  • 在子資料列仍參照的情況下,刪除父資料列或變更其鍵值。
  • 值不符合 CHECK 約束,例如負數量。
  • 為現有資料已經違反該條件的資料表新增外鍵或檢查約束(ALTER TABLE ... ADD CONSTRAINT).
  • 以錯誤的順序載入資料表:子資料表排在父資料表之前。

如何修正

  1. 從訊息中找出條件約束的名稱,再看它強制執行的是什麼。

    SELECT fk.name, OBJECT_NAME(fk.parent_object_id) AS child_table,
           OBJECT_NAME(fk.referenced_object_id) AS parent_table,
           fk.delete_referential_action_desc
    FROM sys.foreign_keys AS fk WHERE fk.name = N'FK_Orders_Customers';
  2. 若是插入,請先確認父資料列存在,或先插入父資料列再插入子資料列。

    SELECT * FROM dbo.Customers WHERE CustomerId = 42;
  3. 若是刪除,請先移除或重新指派子資料列,或在定義外鍵時加上 ON DELETE CASCADE 如果你想連同父資料列一起刪除子資料列。

    DELETE FROM dbo.Orders WHERE CustomerId = 42;
    DELETE FROM dbo.Customers WHERE CustomerId = 42;
  4. 對現有資料新增條件約束時,先找出會違反它的資料列。

    SELECT o.*
    FROM dbo.Orders AS o
    LEFT JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId
    WHERE o.CustomerId IS NOT NULL AND c.CustomerId IS NULL;

安全地重現問題

暫存資料表不能有外鍵,因此重現範例使用了 CHECK 條件約束;違反外鍵時會引發相同的錯誤編號。

CREATE TABLE #p (id int PRIMARY KEY);
CREATE TABLE #c (id int, CHECK (id > 0));
INSERT INTO #c VALUES (-1);  -- Msg 547: The INSERT statement conflicted with the CHECK constraint ...
DROP TABLE #c; DROP TABLE #p;

問題

如何刪除被其他資料表參照的資料列?

請先刪除或更新參照的資料列,或在符合資料語意的情況下,將外鍵改為 ON DELETE CASCADE 或 ON DELETE SET NULL。停用條件約束會留下孤立的資料列。

相關錯誤

更多條件約束與索引鍵錯誤

程式碼和訊息文字來自 Microsoft 資料庫引擎事件和錯誤參考。瀏覽完整的 SQL Server 錯誤清單 或 搜尋每個資料庫錯誤.

// 錯誤更少,修復更快

在 SQLly 裡操作 SQL Server

適用於 macOS、Windows 和 Linux 的原生 SQL 客戶端,具備結構描述感知的 IntelliSense、高風險陳述式防護與清楚的錯誤訊息,適用於 SQL Server 和你使用的其他資料庫。