複数列からなる外部キー(参照整合性)制約

外部キー(参照性合成)制約が複数列になるような場合を考える。

grade
numcolor
1
2
3
class
gnumnumcharge
11田中
12鈴木
13佐藤
14松井
21岡田
student
gnumcnumnumname  …  
111大石友美
112中村優作
113北川陽子
114望月慎太郎
115木村葉子
116藤田智子
121石川大地

gradeは学年、classは組、studentは生徒をそれぞれ意味する。
・grade … num : 学年
・class … gnum : 学年、num : 組
・student … gnum : 学年、cnum : 組、num : 出席番号

  1. 学年テーブル作成・挿入

    次のSQLをファイルとして作成。(grade_setup061.sql)
    
    
    oddtbs=> \i grade_setup061.sql
    ...
    ...
    oddtbs=> select * from grade;
     num | color
    -----+-------
       1 | 青
       2 | 赤
       3 | 緑
    (3 rows)
    
  2. 組テーブル作成・挿入

    次のSQLをファイルとして作成。(class_setup061.sql)
    
    
    oddtbs=> \i class_setup061.sql
    ...
    ...
    oddtbs=> select * from class;
     gnum | num | charge
    ------+-----+--------
        1 |   1 | 田中
        1 |   2 | 佐藤
        1 |   3 | 鈴木
        1 |   4 | 松井
        2 |   1 | 岡田
        2 |   2 | 山本
        2 |   3 | 斎藤
        2 |   4 | 吉田
        3 |   1 | 村山
        3 |   2 | 森田
        3 |   3 | 原田
        3 |   4 | 島田
    (12 rows)
    
  3. 生徒テーブル作成

    次のSQLをファイルとして作成。(student_setup061.sql)
    
    
    oddtbs=> \i student_setup061.sql
    ...
    ...
    oddtbs=> insert into student values(1, 9, 1, '山田太郎', 1, 'A', 60);
    INSERT 0 1
    oddtbs=> select * from student;
     gnum | cnum | num |   name   | gender | blood | score
    ------+------+-----+----------+--------+-------+-------
        1 |    9 |   1 | 山田太郎 |      1 | A     |    60
    (1 row)
    

    1年9組というclassテーブルに存在しない組み合わせが挿入されてしまっている。

  4. 複数列からなる外部キー(参照性合成)制約を適用

    FOREIGN KEY(列の並び) REFERENCES テーブル名(列の並び)
    
    次のSQLをファイルとして作成。(student_setup062.sql)
    
    
    oddtbs=> \i student_setup062.sql
    ...
    ...
    oddtbs=> insert into student values(1, 9, 1, '山田太郎', 1, 'A', 60);
    ERROR:  insert or update on table "student" violates foreign key constraint "student
    DETAIL:  Key (gnum,cnum)=(1,9) is not present in table "class".
    
    ※ gnumとcnumが、classテーブルのgnumとnumにない組み合わせなので拒否された。
    
    oddtbs=> select * from student;
     gnum | cnum | num | name | gender | blood | score
    ------+------+-----+------+--------+-------+-------
    (0 rows)
    
  5. 生徒テーブル挿入

    次のファイルダウンロード。(student_sjis.dat
    oddtbs=> \copy student from student_sjis.dat
    oddtbs=> select * from student;
     gnum | cnum | num |     name     | gender | blood | score
    ------+------+-----+--------------+--------+-------+-------
        1 |    1 |   1 | 大石友美     |      2 | O     |    63
        1 |    1 |   2 | 中村優作     |      1 | O     |    48
        1 |    1 |   3 | 北川陽子     |      2 | A     |    94
        1 |    1 |   4 | 望月慎太郎   |      1 | A     |    53
        1 |    1 |   5 | 木村葉子     |      2 | AB    |    75
        1 |    1 |   6 | 藤田智子     |      2 | A     |    68
        1 |    2 |   1 | 石川大地     |      1 | O     |    74
    ...
    ...
        3 |    4 |   4 | 鷲山綾子     |      2 | B     |    45
        3 |    4 |   5 | 内山絢平     |      1 | AB    |    54
        3 |    4 |   6 | 梶間昇平     |      1 | B     |    58
    (72 rows)