解決問題 SQL Server (2)

教學目標

主要解決當我們在 SQL Server 中更新資料時因為外部索引鍵條件約束所發生的錯誤問題。

重點整理

首先主索引鍵和外部索引鍵是強制執行 SQL Server 資料表中資料完整性的關鍵條件約束。請注意一份資料表只能有一個主索引鍵條件約束,但能有多個外部索引鍵條件約束。

接著一份資料表中會有一個或多個主索引鍵 (Primary Key, PK),主索引鍵的條件約束確保唯一的資料,所以通常定義在識別欄位。此外當我們為資料表指定主索引鍵條件約束時,則 Database Engine 將會自動為主索引鍵建立唯一索引,除了強制資料唯性之外,更能夠讓我們快速的存取資料。

再來兩份資料表之間會有一個或多個外部索引鍵 (Foreign Key, FK),外部索引鍵條件約束並不會自動建立對應的索引,需要手重建立索引,索引可以讓 Database Engine 在外部索引鍵的資料表中快速查尋相關資料,建立索引並非必要,即使資料表之間未定義主索引鍵或外部索引鍵條件約束,仍然可以合併兩個資料表。同時系統會檢查主索引鍵條件約束的變更與相關資料表中的外部索引鍵條件約束,所以我們停用外部索引鍵條件約束,將能夠讓新增、更新和刪除資料速度更快,同時無須依據主索引鍵和外部索引鍵的順序匯入。

至於主索引鍵和外部索引鍵條件約束為何可以確保資料完整性,因為我們需要針對主索引鍵資料表的資料進行更新時,若會讓指到外部索引鍵資料表內資料的連結無效時,則該外部索引鍵條件約束會禁止執行此更新動作,以強制參考完整性。此外若我們需要針對主索引鍵資料表的資料進行刪除時,則在刪除主索引鍵值對應至另一個資料表之外部索引鍵條件約束中的值時,則該外部索引鍵條件約束會禁止執行此刪除動作。

最後當我們遇到因為外部索引鍵條件約束所發生的錯誤問題時,若要解決此問題官方文件則是提供必須要先更新或刪除外部索引鍵資料表中的資料。此時資料筆數少時,則能夠手動調整,但是當資料筆數多時,我們是否能夠停用外部索引鍵條件約束呢?答案是可以,僅需要透過下述 SQL 語法就能夠停用所有或特定外部索引鍵條件約束,以及啟用所有或特定外部索引鍵條件約束。

停用所有外部索引鍵條件約束

1
ALTER TABLE 資料表名稱 NOCHECK CONSTRAINT ALL;

停用特定外部索引鍵條件約束

1
ALTER TABLE 資料表名稱 NOCHECK CONSTRAINT 外部索引鍵;

啟用所有外部索引鍵條件約束

1
ALTER TABLE 資料表名稱 WITH CHECK CHECK CONSTRAINT ALL;

啟用特定外部索引鍵條件約束

1
ALTER TABLE 資料表名稱 WITH CHECK CHECK CONSTRAINT 外部索引鍵;

總結我們透過簡單的 SQL 語法就能夠暫時停用外部索引鍵條件約束,以利我們順利更新或刪除資料,此外當我們完成之後,又可以再透過簡單的 SQL 語法就能夠暫時啟動外部索引鍵條件約束,以利確保系統後續更新或刪除操作時的資料完整性。

相關資源