postgreSQL その28 SQL テーブルの定義その2

postgreSQL その8 課題リストは2018/4/30時点でのオイラの所見

[P.239]

■外部キー制約

外部キー制約は参照整合性制約とも呼ばれ、あるテーブルの列が別のテーブルの列を参照することで実現している。　外部キーを使用することで、例えば、テーブルAのid列がテーブルBのid列を参照し、テーブルBのid列に無い値がテーブルAのid列に入るのを防ぐことができる。

[書式]

create table テーブル名 (列名データ型 references 参照先テーブル名(列名));

create table テーブル名 (列名データ型, foreign key(列名) references 参照先テーブル名(列名));

alter table テーブル名 add constraint 外部キー名 foreign key (列名) references 参照先テーブル名(列名);

削除するときは

alter table テーブル名 drop constraint 外部キー名:

「外部キー名」を指定しないCREATE TABLE の場合は「テーブル名_列名_fkey」という名前で外部キーが作成される。参照先のテーブルの列には、主キー制約かユニーク制約が設定されている必要がある。

[例]

city3テーブルの「name」列をユニーク制約、tb8の「city」列からcity3テーブルの「name」列を参照させる外部キー制約を設定する。

testdb=# select * from city3;
id | name | population | prefecture | 地方 | 県庁所在地 | 政令指定都市 | comment
----+--------+------------+------------+--------+------------+--------------+----------------------
1 | 前橋 | 30 | 群馬 | 関東 | t | f | ぐんまー
2 | 千葉 | 100 | 千葉 | 関東 | t | t | ちばらぎ
3 | 郡山 | 40 | 福島 | 東北 | f | f |
4 | 横浜 | 350 | 神奈川 | 関東 | t | t | オサレ
5 | 福生 | 12 | 東京 | 関東 | f | f | 米軍基地
6 | 那覇 | 30 | 沖縄 | 九州 | t | f | しまんちゅ
7 | 旭川 | 30 | 北海道 | 北海道 | f | f | かなり寒い
8 | 習志野 | 20 | 千葉 | 関東 | f | f | 空挺部隊
9 | 藤沢 | 40 | 神奈川 | 関東 | f | f | 湘南ナンバーぶいぶい
10 | 相模原 | 40 | 神奈川 | 関東 | f | f |
11 | 町田 | 40 | 東京 | 関東 | f | f | 神奈川県じゃないんご
12 | 和歌山 | 25 | 和歌山 | 近畿 | t | f |
(12 rows)
testdb=# select * from tb8;
first | last | city
--------------+--------------+------
鈴木 | 一郎 | 前橋
鈴木 | 次郎 | 前橋
田中 | 次郎 | 横浜
田中 | 一郎 | 前橋
(4 rows)

tb8に外部キー制約で許されている挿入をするんご

tb8に外部キー制約で許されてない挿入をするんご

testdb=# insert into tb8 values ('坂上','田村麻呂','豊原');
ERROR: insert or update on table "tb8" violates foreign key constraint "chinko"
DETAIL: Key (city)=(豊原) is not present in table "city3".

外部キーによって参照されているテーブル（たいていマスターテーブル）の列を削除/更新すると、制約に違反することになる。このような処理を行う場合、エラーにさせる（参照されているデータは変更させない）方法と、参照しているデータも削除/更新してしまう方法の２つを選択できる。　defaultではエラーとなる。　もし、参照先のデータ削除/更新と連動して、参照元のデータも削除/更新する場合は下記の書式を利用する

[書式]

create table テーブル名(列名データ型 references 参照先テーブル名 (列名) [on delete cascade] [on update cascade] );

alter table テーブル名 add constraint 外部キー名 foreign key (列名) references 参照先テーブル名(列名) [on delete cascade] [on update cascade];

ON DELETE CASCADE を付与することにより、参照先のデータの削除と連動して参照元のデータも削除される。　ON UPDATE CASCADE は更新と連動する。

[例]

・デフォルトの場合、city3から横浜を削除しようとすると参照先のtb8で横浜を参照しているからエラーになるか？

testdb=# delete from city3 where name='横浜';
ERROR: update or delete on table "city3" violates foreign key constraint "chinko" on table "tb8"
DETAIL: Key (name)=(横浜) is still referenced from table "tb8".

→エラーになって削除できなかった

testdb=# select * from city3 where name='横浜';
id | name | population | prefecture | 地方 | 県庁所在地 | 政令指定都市 | comment
----+------+------------+------------+------+------------+--------------+---------
4 | 横浜 | 350 | 神奈川 | 関東 | t | t | オサレ
(1 row)

・「ON DELETE CASCADE」と「ON UPDATE CASCADE」を付与

testdb=# alter table tb8 drop constraint chinko;
ALTER TABLE
testdb=# alter table tb8 add constraint chinko foreign key (city) references city3(name) on delete cascade on update cascade;
ALTER TABLE
testdb=# \d tb8
Table "testschema.tb8"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
first | character(10) | | not null |
last | character(10) | | not null |
city | character varying(10) | | |
Indexes:
"tb8_pkey" PRIMARY KEY, btree (first, last)
Foreign-key constraints:
"chinko" FOREIGN KEY (city) REFERENCES city3(name) ON UPDATE CASCADE ON DELETE CASCADE

city3の「横浜」を「横濱」に変更する

testdb=# update city3 set name = '横濱' where name = '横浜';
UPDATE 1
testdb=# select * from city3;
id | name | population | prefecture | 地方 | 県庁所在地 | 政令指定都市 | comment
----+--------+------------+------------+--------+------------+--------------+----------------------
1 | 前橋 | 30 | 群馬 | 関東 | t | f | ぐんまー
2 | 千葉 | 100 | 千葉 | 関東 | t | t | ちばらぎ
3 | 郡山 | 40 | 福島 | 東北 | f | f |
5 | 福生 | 12 | 東京 | 関東 | f | f | 米軍基地
6 | 那覇 | 30 | 沖縄 | 九州 | t | f | しまんちゅ
7 | 旭川 | 30 | 北海道 | 北海道 | f | f | かなり寒い
8 | 習志野 | 20 | 千葉 | 関東 | f | f | 空挺部隊
9 | 藤沢 | 40 | 神奈川 | 関東 | f | f | 湘南ナンバーぶいぶい
10 | 相模原 | 40 | 神奈川 | 関東 | f | f |
11 | 町田 | 40 | 東京 | 関東 | f | f | 神奈川県じゃないんご
12 | 和歌山 | 25 | 和歌山 | 近畿 | t | f |
4 | 横濱 | 350 | 神奈川 | 関東 | t | t | オサレ
(12 rows)

→前橋な人たちが消えた

■チェック制約

チェック制約は、ある列に対して特定の条件を満たす値のみ許可する制約。

[書式]

create table テーブル名 (列名データ型 check (条件式));

alter table テーブル名 add constraint チェック制約名 check (条件式);

「条件式」には、特定の列に対する指揮や関数を指定する。「条件式」の結果が真になるものだけを許可することになる。

「条件式」はANDでつなげることで、複数の式を一度に指定することも可能。　なお、「チェック制約名」を指定しない create table の場合は、「テーブル名_列名_check」という名前でチェック制約名が自動で付与される。　

[例]

testdb=# insert into city3 values (13,'キチガイ島',0,'キチガイ県','きち',false,false,null);
ERROR: new row for relation "city3" violates check constraint "check_puni"
DETAIL: Failing row contains (13, キチガイ島, 0, キチガイ県, きち, f, f, null).

■ドメイン制約

ドメイン制約は、チェック制約を伴った独特のデータ型にどんな制約を付与するのかを定義する。　作成したドメイン制約については、通常のデータ型と同じように扱うことができる。

[例]

int型を対象に0より大きいという制約を付与して使用する。

testdb=# create domain int_p as integer check (value > 0);
CREATE DOMAIN
testdb=# create table tb9 (c1 int_p);
CREATE TABLE
testdb=# \d tb9
Table "testschema.tb9"
Column | Type | Collation | Nullable | Default
--------+-------+-----------+----------+---------
c1 | int_p | | |
testdb=# insert into tb9 values (1);
INSERT 0 1
testdb=# insert into tb9 values (-1);
ERROR: value for domain int_p violates check constraint "int_p_check"
testdb=# select * from tb9;
c1
----
1
(1 row)

postgreSQL その28 SQL テーブルの定義 その2

■外部キー制約

■チェック制約

■ドメイン制約

postgreSQL その28 SQL テーブルの定義その2