【關於外鍵約束的一些常見問題】

【關於外鍵約束的一些常見問題】

 

/********************************************************************

Author:js_szy

Date:2010-11-08

 

Version:

    Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)

       Oct 14 2005 00:33:37

       Copyright (c) 1988-2005 Microsoft Corporation

       Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

 

********************************************************************/

--> 測試資料: [tb]

if OBJECT_ID('[tb]') is not null DROP TABLE [tb]

go

--> 測試資料: [ta]

if OBJECT_ID('[ta]') is not null DROP TABLE [ta]

go

 

CREATE TABLE [ta]

(

    id int PRIMARY KEY,

    name varchar(1)

)

 

CREATE TABLE [tb]

(

    id1 int CONSTRAINT fk_1 FOREIGN KEY REFERENCES  ta(id),

    name1 varchar(1)

   

)

 

INSERT INTO [ta]

SELECT 1,'a' UNION ALL

SELECT 2,'b'

 

INSERT INTO [tb]

SELECT 1,'q' UNION ALL

SELECT 1,'w' UNION ALL

SELECT 2,'s'

 

/*******************************************************************/

一、建立外鍵約束：

 

1、建立表時建外鍵約束：

 

    CREATE TABLE[tb]

    (

       id1 int CONSTRAINT fk_1 FOREIGN KEY REFERENCES  ta(id),

       name1 varchar(1)

      

    )

 

2、在已有表上建立約束

 

    ALTER TABLEtb ADD CONSTRAINT fk_1 FOREIGN KEY (id1) REFERENCES ta(id)

 

 

/*******************************************************************/

二、刪除約束：

 

    ALTER TABLEtb DROP CONSTRAINT fk_1

/*******************************************************************/

三、下面介紹一下一些常見的錯誤：

 

1、先建子表

 

    /*

    錯誤資訊

       訊息1767，層級16，狀態0，第1 行

       外鍵'FK__tb__id1__7814D14C' 引用了無效的表'ta'。

       訊息1750，層級16，狀態0，第1 行

       無法建立約束。請參閱前面的錯誤訊息。

 

    解決方案：

       先建主表

    */

 

2、先刪主表

    /*

    錯誤資訊:

       訊息3726，層級16，狀態1，第2 行

       無法刪除對象'ta'，因為該對象正由一個FOREIGN KEY 約束引用。

       訊息2714，層級16，狀態6，第3 行

       資料庫中已存在名為'ta' 的對象。

 

    解決方案：

       先刪子表

    */

 

3、插入子表資料，主表中不存在子表插入的索引值

 

    INSERT INTO [tb]

    SELECT 5,'q'      -- 5 在主表中不存在

 

    /*

       錯誤資訊：

           訊息547，層級16，狀態0，第1 行

          INSERT 語句與FOREIGN KEY 約束"FK__tb__id1__5D60DB10"衝突。該衝突發生於資料庫"szy"，表"dbo.ta", column 'id'。

           語句已終止。

      

       解決方案：

           必須先插入主表，再插入子表

    */

 

4、先刪除主表資料

 

    delete from ta where id=1

    /*

    錯誤資訊：

       訊息547，層級16，狀態0，第68 行

       DELETE 語句與REFERENCE 約束"FK__tb__id1__047AA831"衝突。該衝突發生於資料庫"szy"，表"dbo.tb", column 'id1'。

       語句已終止。

 

    解決方案：

       先刪子表，再刪主表

       delete from tb where id1=1

       delete from ta where id=1

    */

 

5、在已有資料的表上添加外鍵約束

    (如果表中存在主表與子表沒有關聯的索引值，將報如下錯誤)

 

    ALTER TABLEtb ADD FOREIGN KEY (id1) REFERENCES ta(id) 

 

    /*

    訊息547，層級16，狀態0，第20 行

    ALTER TABLE 語句與FOREIGN KEY 約束"FK__tb__id1__23F3538A"衝突。該衝突發生於資料庫"szy"，表"dbo.ta", column 'id'。

    */

 

/*******************************************************************/

 

總結：對有外鍵約束表的操作順序，先建主，再建子。先刪子，再刪主。