外部キー（参照整合性）制約

問題点①

社員テーブル（emp）に部署番号（depcode）と部署名（depname）という列を追加する。

  1. 次のＳＱＬをファイルとして作成。（emp_setup051.sql）

    

    2. 

  2. 

    ログイン画面から実行。
    

    oddtbs=> \i emp_setup051.sql
...
...
oddtbs=> select * from emp;
  code  | depcode | depname |   name   | gender
--------+---------+---------+----------+--------
 564001 |    1001 | 総務部  | 鈴木義男 |      1
 564002 |    1001 | 営業部  | 斎藤洋子 |      2
(2 rows)

    

    3. 



同じ部署番号（depcode）に異なる部署名（depname）が対応するという矛盾が起きてしまっている。



テーブルを分割


部署の情報を管理する別のテーブル（dep）を定義する。


    

  1. 

    次のＳＱＬをファイルとして作成。（dep_setup051.sql）
    

    

    2. 

  2. 

    ログイン画面から実行。
    

    oddtbs=> \i dep_setup051.sql
...
...
oddtbs=> select * from dep;
 code |  name
------+--------
 1001 | 総務部
 1002 | 営業部
 1003 | 開発部
(3 rows)

    

    3. 




問題点②


社員テーブル（emp）は部署テーブル（dep）の部署番号（code）と同じ値の列（depcode）のみをもつようにする。


    

  1. 

    次のＳＱＬをファイルとして作成。（emp_setup052.sql）
    

    

    2. 

  2. 

    ログイン画面から実行。
    

    oddtbs=> \i emp_setup052.sql
...
...
oddtbs=> select * from emp;
  code  | depcode |   name   | gender
--------+---------+----------+--------
 564001 |    1002 | 鈴木義男 |      1
 564002 |    1003 | 斎藤洋子 |      2
 564003 |    1001 | 山田美樹 |      2
(3 rows)

oddtbs=> insert into emp values(564004, 1004, '田中秀明', 1);
INSERT 0 1
oddtbs=> select * from emp;
  code  | depcode |   name   | gender
--------+---------+----------+--------
 564001 |    1002 | 鈴木義男 |      1
 564002 |    1003 | 斎藤洋子 |      2
 564003 |    1001 | 山田美樹 |      2
 564004 |    1004 | 田中秀明 |      1
(4 rows)

    

    3. 



empテーブルのdepcode列に、depテーブルのcode列にない値がはいってしまっている。



外部キー（参照整合性）制約


外部キー制約により、empテーブルのdepcode列を、depテーブルのcode列のある値に一致するように制限する。


列名 型 REFERENCES テーブル名(列名) ... ,



    

  1. 

    次のＳＱＬをファイルとして作成。（emp_setup053.sql）
    

    

    2. 

  2. 

    ログイン画面から実行。
    

    oddtbs=> \i emp_setup053.sql
...
...
oddtbs=> select * from emp;
  code  | depcode |   name   | gender
--------+---------+----------+--------
 564001 |    1002 | 鈴木義男 |      1
 564002 |    1003 | 斎藤洋子 |      2
 564003 |    1001 | 山田美樹 |      2
(3 rows)

oddtbs=> insert into emp values(564004, 1004, '田中秀明', 1);
ERROR:  insert or update on table "emp" violates foreign key constraint "emp_depcode_fkey"
DETAIL:  Key (depcode)=(1004) is not present in table "dep".

※ depcode列にdepテーブルのcode列にない値が指定されたため拒否された。

oddtbs=> insert into emp values(564004, 1002, '田中秀明', 1);
INSERT 0 1
oddtbs=> select * from emp;
  code  | depcode |   name   | gender
--------+---------+----------+--------
 564001 |    1002 | 鈴木義男 |      1
 564002 |    1003 | 斎藤洋子 |      2
 564003 |    1001 | 山田美樹 |      2
 564004 |    1002 | 田中秀明 |      1
(4 rows)

    

    3.