制約

テーブルに不正なデータが入らないように制限することができる。これを制約という。
制約には様々な種類がある。
まずは列に付加する形で制約を記述する。

列名 型 制約
種類意味
NOT NULLNULLを禁止
UNIQUE重複を禁止
CHECK条件を指定
PRIMARY KEYNOT NULLかつUNIQUE（ただし１テーブルに１つ）

NOT NULL制約, UNIQUE制約, CHECK制約

  1. ログイン画面から、第１章のemp_create011.sqlを実行

    oddtbs=> \i emp_create011.sql
...

  2. ログイン画面から実行。

    oddtbs=> insert into emp values(564001, '鈴木義男', 1, 58, '11-1111');
INSERT 0 1
oddtbs=> insert into emp values(564001, '斎藤洋子', 2, 44, '22-2222');
INSERT 0 1
oddtbs=> insert into emp (name, tel) values('山田美樹', '33-3333');
INSERT 0 1
oddtbs=> insert into emp values(564004, '田中秀明', 3, 35, '44-4444');
INSERT 0 1
oddtbs=> select * from emp;
  code  |   name   | gender | age |   tel
--------+----------+--------+-----+---------
 564001 | 鈴木義男 |      1 |  58 | 11-1111
 564001 | 斎藤洋子 |      2 |  44 | 22-2222
        | 山田美樹 |        |     | 33-3333
 564004 | 田中秀明 |      3 |  35 | 44-4444
(4 rows)

    ※ 次のような問題が起こっている。
    ・鈴木さんと斉藤さんのcode列が重複している。
    ・山田さんの行にNULLがある。特にcode列は問題。
    ・田中さんのgender列が3になっている。

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

    

    4. 

  4. 

    ログイン画面から実行。
    

    oddtbs=> \i emp_create041.sql
...

    

    5. 

  5. 

    2と同じことを再度実行。
    

    oddtbs=> insert into emp values(564001, '鈴木義男', 1, 58, '11-1111');
INSERT 0 1

oddtbs=> insert into emp values(564001, '斎藤洋子', 2, 44, '22-2222');
ERROR:  duplicate key value violates unique constraint "emp_code_key"
※ UNIQUE制約によって拒否された。

oddtbs=> insert into emp (name, tel) values('山田美樹', '33-3333');
ERROR:  null value in column "code" violates not-null constraint
※ NOT NULL制約によって拒否された。

oddtbs=> insert into emp values(564004, '田中秀明', 3, 35, '44-4444');
ERROR:  new row for relation "emp" violates check constraint "emp_gender_check"
※ CHECK制約によって拒否された。

oddtbs=> select * from emp;
  code  |   name   | gender | age |   tel
--------+----------+--------+-----+---------
 564001 | 鈴木義男 |      1 |  58 | 11-1111
(1 row)

    

    6. 




制約の確認


    

  1. 

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

    

    2. 

  2. 

    ログイン画面から実行。
    

    oddtbs=> \i showconst041.sql
           conname            | conrelid | contype | conkey
------------------------------+----------+---------+---------
 cardinal_number_domain_check | -        | c       |
 emp_gender_check             | emp      | c       | {3}
 emp_code_key                 | emp      | u       | {1}
(3 rows)

    

    3. 




主キー(PRIMARY KEY)制約


主キー(PRIMARY KEY)制約はNOT NULLかつUNIQUEであるが、１つのテーブルにつき１つしかつけられない。


    

  1. 

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

    

    2. 

  2. 

    ログイン画面から実行。
    

    oddtbs=> \i emp_create042.sql
...

    

    oddtbs=> \i showconst041.sql
           conname            | conrelid | contype | conkey
------------------------------+----------+---------+---------
 cardinal_number_domain_check | -        | c       |
 emp_gender_check             | emp      | c       | {3}
 emp_age_check                | emp      | c       | {4}
 emp_pkey                     | emp      | p       | {1}
(4 rows)

    

    3. 




テーブル制約



ここまでは単独の列についての制約だったが、複数列が関係する制約が必要な場合もある。

今回は社員テーブル（emp）に年齢（age）と給料（salary）列を追加し、次の条件をつける。

・給料は年齢の１万倍以上で２万倍以下。
この場合、複数列が関係する制約が必要となる。そのような制約をテーブル制約という。

それに対し、これまでのような単独の列の制約は列制約という。

テーブル制約は、独立した列の位置に記述する。




CREATE TABLE テーブル名(列名 型, 列名 型, ..., 制約, 制約, ...);



    

  1. 

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

    

    2. 

  2. 

    ログイン画面から実行。
    

    oddtbs=> \i emp_create043.sql
...

    

    oddtbs=> \i showconst041.sql
           conname            | conrelid | contype | conkey
------------------------------+----------+---------+---------
 cardinal_number_domain_check | -        | c       |
 emp_gender_check             | emp      | c       | {3}
 emp_age_check                | emp      | c       | {4}
 emp_check                    | emp      | c       | {6,4}
 emp_pkey                     | emp      | p       | {1}
(5 rows)

※ 2列分の制約であることが分かる。

    

    3. 

  3. 

    ログイン画面から実行。
    

    oddtbs=> insert into emp values(564001, '鈴木義男', 1, 58, '11-1111', 480000);
ERROR:  new row for relation "emp" violates check constraint "emp_check"
※ 年齢と給料が条件を満たさないので拒否された。

oddtbs=> insert into emp values(564001, '鈴木義男', 1, 58, '11-1111', 600000);
INSERT 0 1
※ 今度はＯＫ。

    

    4. 




制約の名前


制約には自分で名前をつけることができる。


CONSTRAINT 制約名 制約;



    

  1. 

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

    

    2. 

  2. 

    ログイン画面から実行。
    

    oddtbs=> \i emp_create044.sql
...

    

    oddtbs=> \i showconst041.sql
           conname            | conrelid | contype | conkey
------------------------------+----------+---------+---------
 cardinal_number_domain_check | -        | c       |
 emp_gender_check             | emp      | c       | {3}
 emp_age_check                | emp      | c       | {4}
 emp_salary                   | emp      | c       | {6,4}
 emp_pkey                     | emp      | p       | {1}
(5 rows)

※ 自分で決めた名前になっている。

    

    3. 




複数列からなる主キー


主キーが複数列からなる場合もある。その場合、テーブル制約として次のように記述する。


PRIMARY KEY (列の並び)




今回は生徒テーブル（student）を作成し、次の列をもたせることにする。

・学年（gnum）

・組（cnum）

・出席番号（num）

この場合、これら３つの列で１つの主キーを形成する必要がある。




    

  1. 

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

    

    2. 

  2. 

    ログイン画面から実行。
    

    oddtbs=> \i student_create041.sql
...

    

    oddtbs=> \i showconst041.sql
           conname            | conrelid | contype | conkey
------------------------------+----------+---------+---------
 cardinal_number_domain_check | -        | c       |
 emp_gender_check             | emp      | c       | {3}
 emp_age_check                | emp      | c       | {4}
 emp_salary                   | emp      | c       | {6,4}
 emp_pkey                     | emp      | p       | {1}
 student_gnum_check           | student  | c       | {1}
 student_cnum_check           | student  | c       | {2}
 student_num_check            | student  | c       | {3}
 student_pkey                 | student  | p       | {1,2,3}
(9 rows)

    

    3. 

  3. 

    ログイン画面から実行。
    

    oddtbs=> insert into student values(1, 4, 10, '山本信治');
INSERT 0 1
oddtbs=> insert into student values(2, 4, 10, '森田優子');
INSERT 0 1
oddtbs=> insert into student values(1, 4, 10, '安部俊行');
ERROR:  duplicate key value violates unique constraint "student_pkey"
※ 主キー制約によって拒否された。

    

    4.