外部キーの付与方法

外部キーの付与方法について説明します。
以下の社員テーブルの部門番号に、部門テーブル.部門番号への外部キーを付与ししていきます。

部門テーブル

No	項目名（論理）	項目名（物理）	型	制約
1	部門番号	DIV_NO	INT	PK、NOT NULL
2	部門名	DIV_NAME	VARCHAR(30)
4	削除フラグ	DEL_FLG	INT


社員テーブル

No	項目名（論理）	項目名（物理）	型	制約
1	社員番号	EMP_NO	INT	PK、NOT NULL
2	氏名	EMP_NAME	VARCHAR(30)
3	部門番号	DIV_NO	INT
4	削除フラグ	DEL_FLG	INT

外部キーの付与

ここでは外部キーを付与するSQLを説明します。
構文は以下の通りです。
構文の詳細は公式サイトを参照ください。

【文法】

【実行例】

実際にSQLを実行し外部キーを付与します。
SQLは以下の通りです。

ALTER TABLE MST_EMP
  ADD CONSTRAINT FK_EMP_DIV_NO_01 FOREIGN KEY 
(
    DIV_NO
)
REFERENCES MST_DIV
(
    DIV_NO
)
; 

実行結果は以下になります。

SQL> ALTER TABLE MST_EMP
   2   ADD CONSTRAINT FK_EMP_DIV_NO_01 FOREIGN KEY 
   3 (
   4     DIV_NO
   5 )
   6 REFERENCES MST_DIV
   7 (
   8     DIV_NO
   9 )
  10 ;

DB250000I: コマンドは正常に完了しました。
  

外部キーが付与されました。
カタログを検索すると外部キーが追加されていることが確認できます。

SQL> SELECT NAME, CONSTRAINTYP
   2   FROM SYSIBM.SYSTABCONST
   3  WHERE TBNAME       = 'MST_EMP'
   4    AND CONSTRAINTYP = 'F'
   5 ;


NAME                                                       CONSTRAINTYP 
---------------------------------------------------------- -------------
FK_EMP_DIV_NO_01                                           F            

外部キー制約が有効になっているか確認します。
外部キーの参照先の部門コードに値が存在するデータと存在しないデータをINSERTします。
存在しないデータは、エラーになることが確認できます。

SQL> SELECT * FROM MST_DIV;


       DIV_NO DIV_NAME                                  DEL_FLG
------------- ----------------------------------- -------------
         1001 営業部                                          0
         2001 経理部                                          0

SQL> INSERT INTO MST_EMP VALUES(21006, '山田　陸', 2001, 0);

DB250000I: コマンドは正常に完了しました。


SQL> INSERT INTO MST_EMP VALUES(21007, '渡辺　澪', 3001, 0);

行 1 の近くでエラー:
SQL0530N  FOREIGN KEY "XXXX.MST_EMP.FK_EMP_DIV_NO_01"
 の挿入または更新の値は、親表の親キーの値と同じではありません。
  

外部キーの削除

　外部キーの削除のSQLを説明します。
　構文は以下の通りです。

【文法】

【実行例】

実際にSQLを実行し外部キーを削除します。
SQLは以下の通りです。

ALTER TABLE MST_EMP
  DROP CONSTRAINT FK_EMP_DIV_NO_01 
; 

SQLの実行結果は以下の通りです。
これでPKが削除されました。

SQL> ALTER TABLE MST_EMP
   2   DROP CONSTRAINT FK_EMP_DIV_NO_01 
   3 ; 

DB250000I: コマンドは正常に完了しました。

SQL> SELECT NAME, CONSTRAINTYP
   2   FROM SYSIBM.SYSTABCONST
   3  WHERE TBNAME       = 'MST_EMP'
   4    AND CONSTRAINTYP = 'F'
   5 ;

取り出された行はありません。