Sql Server有主外鍵關係時添加、刪除資料

標籤：foreign   check   from   刪除   select   pre   key   comm   ble   

當表之間有主外鍵關係時刪除資料會被約束，添加、刪除失敗

解決辦法，我們可以先把主外鍵關係的檢查約束給關掉 → 然後刪除資料 → 之後再把約束開啟

查詢出關掉所有外鍵約束的語句

SELECT ‘ALTER TABLE ‘ + O.NAME + ‘ NOCHECK CONSTRAINT [‘ + F.NAME + ‘];‘ AS COMMAND FROM SYS.FOREIGN_KEYS F INNER JOIN SYS.ALL_OBJECTS O ON F.PARENT_OBJECT_ID = O.OBJECT_ID WHERE O.TYPE = ‘U‘ AND F.TYPE = ‘F‘;

查詢出開啟所有外鍵約束的語句，與上面的區別是將 NOCHECK 改為 CHECK

SELECT ‘ALTER TABLE ‘ + O.NAME + ‘ CHECK CONSTRAINT [‘ + F.NAME + ‘];‘ AS COMMAND FROM SYS.FOREIGN_KEYS F INNER JOIN SYS.ALL_OBJECTS O ON F.PARENT_OBJECT_ID = O.OBJECT_ID WHERE O.TYPE = ‘U‘ AND F.TYPE = ‘F‘;

 

Sql Server有主外鍵關係時添加、刪除資料