OSSDB標準教科書
[http://oss-db.jp/ossdbtext/text.shtml:embed:cite]
6章 データベース定義の応用
主キー
-
UNIQUE
- 表のデータを一意に特定できる1つ以上の列
-
主キー
- UNIQUEで、かつNOT NULL
-
prod表のprod_idは主キー
SELECT * FROM prod WHERE prod_id = 1;
prod_id | prod_name | price
---------+-----------+-------
1 | みかん | 50
(1 row)
- orders表のprod_idは主キーでない
SELECT *
FROM orders
WHERE prod_id = 1;
order_id | order_date | customer_id | prod_id | qty
----------+----------------------------+-------------+---------+-----
1 | 2019-01-28 23:04:57.664912 | 1 | 1 | 10
4 | 2019-01-28 23:04:57.742378 | 2 | 1 | 3
(2 rows)
主キーを指定する
- CREATE TABLE文で指定
- ALTER TABLE文で指定
\d prod
Table "public.prod"
Column | Type | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
prod_id | integer | | |
prod_name | text | | |
price | integer | | |
ALTER TABLE prod ADD PRIMARY KEY(prod_id);
\d prod
Table "public.prod"
Column | Type | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
prod_id | integer | | not null |
prod_name | text | | |
price | integer | | |
Indexes:
"prod_pkey" PRIMARY KEY, btree (prod_id)
-
prod_idが主キーになる
- 主キーなので NOT NULLになる
- 暗黙的にB木インデックス
prod_pkey
が張られる
- 他のにも張っておく
ALTER TABLE orders ADD PRIMARY KEY(order_id);
ALTER TABLE customer ADD PRIMARY KEY(customer_id);
\d orders
Table "public.orders"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+---------
order_id | integer | | not null |
order_date | timestamp without time zone | | |
customer_id | integer | | |
prod_id | integer | | |
qty | integer | | |
Indexes:
"orders_pkey" PRIMARY KEY, btree (order_id)
\d customer
Table "public.customer"
Column | Type | Collation | Nullable | Default
---------------+---------+-----------+----------+---------
customer_id | integer | | not null |
customer_name | text | | |
Indexes:
"customer_pkey" PRIMARY KEY, btree (customer_id)
主キーの動作を確認する
- NULLが許されない
INSERT INTO prod(prod_name, price) VALUES ('すいか', 100);
2019-02-10 10:17:35.052 UTC [43] ERROR: null value in column "prod_id" violates not-null constraint
2019-02-10 10:17:35.052 UTC [43] DETAIL: Failing row contains (null, すいか, 100).
2019-02-10 10:17:35.052 UTC [43] STATEMENT: INSERT INTO prod(prod_name, price) VALUES (' すいか', 100);
ERROR: null value in column "prod_id" violates not-null constraint
DETAIL: Failing row contains (null, すいか, 100).
-
重複も許されない
- prod_id=3のメロンが既にいる
INSERT INTO prod(prod_id, prod_name, price) VALUES (3, 'すいか', 100); -- duplication
2019-02-10 10:17:46.932 UTC [43] ERROR: duplicate key value violates unique constraint "prod_pkey"
2019-02-10 10:17:46.932 UTC [43] DETAIL: Key (prod_id)=(3) already exists.
2019-02-10 10:17:46.932 UTC [43] STATEMENT: INSERT INTO prod(prod_id, prod_name, price) VALUES (3, 'すいか', 100);
ERROR: duplicate key value violates unique constraint "prod_pkey"
DETAIL: Key (prod_id)=(3) already exists.
複数列からなる主キー
- 複合主キー、複合キーと呼ばれるやつ
-
「1年2組出席番号3番号」みたいなやつ
- 3つ揃ってはじめて生徒を特定できる
外部キー
-
他の表の主キーを参照してるやつ
- 「他の表の主キー」…参照キー
- 参照すること…外部キー参照
- 【補】こいつはNULLABLE
参照整合性制約
-
外部キー制約・参照整合性制約
- 外部キーの値が参照キーに存在することを保証すること
-
禁止
- 参照キーに存在しない値をINSERT
- 参照キーに存在しない値にUPDATE
- 外部キー参照されている参照キーの削除
外部キーを指定する
- CREATE TABLE文で設定
- ALTER TABLE文で指定
\d orders
Table "public.orders"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+---------
order_id | integer | | not null |
order_date | timestamp without time zone | | |
customer_id | integer | | |
prod_id | integer | | |
qty | integer | | |
Indexes:
"orders_pkey" PRIMARY KEY, btree (order_id)
ALTER TABLE orders ADD FOREIGN KEY(prod_id) REFERENCES prod(prod_id);
\d orders
Table "public.orders"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+---------
order_id | integer | | not null |
order_date | timestamp without time zone | | |
customer_id | integer | | |
prod_id | integer | | |
qty | integer | | |
Indexes:
"orders_pkey" PRIMARY KEY, btree (order_id)
Foreign-key constraints:
"orders_prod_id_fkey" FOREIGN KEY (prod_id) REFERENCES prod(prod_id)
- 他のもやっておく
ALTER TABLE orders ADD FOREIGN KEY(customer_id) REFERENCES customer(customer_id);
\d orders
Table "public.orders"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+---------
order_id | integer | | not null |
order_date | timestamp without time zone | | |
customer_id | integer | | |
prod_id | integer | | |
qty | integer | | |
Indexes:
"orders_pkey" PRIMARY KEY, btree (order_id)
Foreign-key constraints:
"orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
"orders_prod_id_fkey" FOREIGN KEY (prod_id) REFERENCES prod(prod_id)
外部キーの動作を確認する
-
参照キーに存在しない値でのINSERT
-- prod_id=5の商品はない INSERT INTO orders(order_id, order_date, customer_id, prod_id, qty) VALUES (6, now(), 3, 5, 10);
2019-02-10 10:47:25.479 UTC [43] ERROR: insert or update on table "orders" violates foreign key constraint "orders_prod_id_fkey"
2019-02-10 10:47:25.479 UTC [43] DETAIL: Key (prod_id)=(5) is not present in table "prod".
2019-02-10 10:47:25.479 UTC [43] STATEMENT: INSERT
INTO orders(order_id, order_date, customer_id, prod_id, qty)
VALUES (6, now(), 3, 5, 10);
ERROR: insert or update on table "orders" violates foreign key constraint "orders_prod_id_fkey"
DETAIL: Key (prod_id)=(5) is not present in table "prod".
- 参照キーに存在しない値へのUPDATE
UPDATE orders
SET prod_id = 5
WHERE order_id = 5;
2019-02-10 10:49:23.459 UTC [43] ERROR: insert or update on table "orders" violates foreign key constraint "orders_prod_id_fkey"
2019-02-10 10:49:23.459 UTC [43] DETAIL: Key (prod_id)=(5) is not present in table "prod".
2019-02-10 10:49:23.459 UTC [43] STATEMENT: UPDATE orders
SET prod_id = 5
WHERE order_id = 5;
ERROR: insert or update on table "orders" violates foreign key constraint "orders_prod_id_fkey"
DETAIL: Key (prod_id)=(5) is not present in table "prod".
- 外部キー参照されている参照キーの削除
DELETE
FROM prod
WHERE prod_id = 1;
2019-02-10 10:50:38.262 UTC [43] ERROR: update or delete on table "prod" violates foreign key constraint "orders_prod_id_fkey" on table "orders"
2019-02-10 10:50:38.262 UTC [43] DETAIL: Key (prod_id)=(1) is still referenced from table "orders".
2019-02-10 10:50:38.262 UTC [43] STATEMENT: DELETE
FROM prod
WHERE prod_id = 1;
ERROR: update or delete on table "prod" violates foreign key constraint "orders_prod_id_fkey" on table "orders"
DETAIL: Key (prod_id)=(1) is still referenced from table "orders".
-
【補】被参照テーブルをDROP TABLEすると、外部キー制約が消える
CREATE TABLE文で主キー、外部キーを設定する
- 参照先を先にCREATEする必要あり
CREATE TABLE prod_second
(prod_id INT PRIMARY KEY,
prod_name TEXT,
price INT);
CREATE TABLE customer_second
(customer_id INT PRIMARY KEY,
customer_name TEXT);
CREATE TABLE orders_second
(order_id INT PRIMARY KEY,
order_date TIMESTAMP,
customer_id INT REFERENCES customer_second(customer_id),
prod_id INT REFERENCES prod_second(prod_id),
qty INT);
主キー、外部キーは必要か?
- 断言は避けている
-
利点
-
フールプルーフ
- 重複値の挿入の防止
- 誤削除の防止
-
-
欠点
-
一時的な不整合状態を許さないため、メンテナンスで不便
- UNIQUE列の入れ替えなど
-
- 基本的には使い、運用上問題があるときのみ外すとよい
- 少なくとも設計上は重要
-
【補】UNIQUE列の入れ替えは下記SQLでできるDBMSもあるらしい
- 原子性を考えれば当然できるべき
- postgresは駄目みたい
UPDATE prod
SET prod_id = (CASE WHEN prod_id = 1 THEN 2
WHEN prod_id = 2 THEN 1
ELSE prod_id
END);
【補】DEFERRABLE制約
- CREATE TABLE時に主キーにDEFERRABLEをつけておくと、上記のSQLで主キーを入れ替えられる
CREATE TABLE swap_sample
(
id INT PRIMARY KEY DEFERRABLE,
num INT
);
\d swap_sample
Table "public.swap_sample"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
num | integer | | |
Indexes:
"swap_sample_pkey" PRIMARY KEY, btree (id) DEFERRABLE
INSERT INTO swap_sample(id, num) VALUES(1, 1);
INSERT INTO swap_sample(id, num) VALUES(2, 2);
SELECT * FROM swap_sample;
id | num
----+-----
1 | 1
2 | 2
(2 rows)
UPDATE swap_sample
SET id = (CASE WHEN id = 1 THEN 2
WHEN id = 2 THEN 1
ELSE id
END);
-
怒られない
UPDATE 2
SELECT * FROM swap_sample;
id | num
----+-----
2 | 1
1 | 2
(2 rows)
正規化
- 本書では詳しくはやりません
-
表をシンプルな複数の表に分割していく
- データの重複がなくなる
- 修正・削除、追加等を行ったときに問題が発生する可能性を低くする
NULLについて
-
【補】「値がない」ことを表す印
- あくまで値ではない
-
【補】UNKNOWNとNOT APPLICABLE の2種類が考えられるが、
RDBやSQLの世界で殊更に区別することはない-
単に不明なのがUNKNOWN
- 例: 両姫お姉ちゃんの生前の誕生日
-
定義不可能なのがNOT APPLICABLE
- 例: 神楽様の血液型
-
NOT NULL制約
- 列にNULLを許さない
- 主キーの必要条件なので、主キーを設定すると暗黙に付与される
NULLの判定
-
「値がない」ので、値があること前提のあらゆる演算子や関数が正常に評価されない
- 【補】true, false, unknownの三値論理
- 【補】排中律が成り立たないので注意
SELECT NAME
FROM kagura
WHERE blood_type = NULL;
name
------
(0 rows)
SELECT NAME
FROM kagura
WHERE blood_type <> NULL;
name
------
(0 rows)
IS NULL
、IS NOT NULL
演算子を使う
SELECT NAME
FROM kagura
WHERE blood_type IS NULL;
name
--------
神楽
両姫
(2 rows)
SELECT NAME
FROM kagura
WHERE blood_type IS NOT NULL;
name
-------------------
雪泉
叢
夜桜
...
蓮華
華毘
華風流
(27 rows)
NULLの集約関数での取り扱い
- 基本対象外
count(カラム名)
でも対象外count(*)
だけは例外的に対象
SELECT count(*)
FROM kagura;
count
-------
29
(1 row)
SELECT count(blood_type)
FROM kagura;
count
-------
27
(1 row)
空文字
- 空文字は空文字という値なので普通に扱える
シーケンス
-
連番を生成する
- 自動増加キーとかに使う
シーケンスの作成
CREATE SEQUENCE order_id_seq;
\d
List of relations
Schema | Name | Type | Owner
--------+--------------+----------+----------
public | customer | table | postgres
public | digits | table | postgres
public | kagura | table | postgres
+ public | order_id_seq | sequence | postgres
public | orders | table | postgres
public | prod | table | postgres
public | seq | view | postgres
public | staff | table | postgres
public | staff_id_seq | sequence | postgres
public | zip | table | postgres
(10 rows)
-
SEQUENCEのデフォルト値
- 開始値: 1
- 増加量: 1
- 最大値: 2の63乗 - 1
シーケンスの操作
SELECT * FROM order_id_seq;
last_value | log_cnt | is_called
------------+---------+-----------
1 | 0 | f
(1 row)
-
未使用で現在値を読み出すと怒られる
SELECT currval('order_id_seq');
2019-02-10 11:22:53.333 UTC [43] ERROR: currval of sequence "order_id_seq" is not yet defined in this session
2019-02-10 11:22:53.333 UTC [43] STATEMENT: SELECT currval('order_id_seq');
ERROR: currval of sequence "order_id_seq" is not yet defined in this session
-
一つ進める
SELECT nextval('order_id_seq');
nextval
---------
1
(1 row)
SELECT currval('order_id_seq');
- 今度は怒られない
currval
---------
1
(1 row)
SELECT * FROM order_id_seq;
last_value | log_cnt | is_called
------------+---------+-----------
1 | 32 | t
(1 row)
- もう一つ進める
SELECT nextval('order_id_seq');
nextval
---------
2
(1 row)
SELECT currval('order_id_seq');
currval
---------
2
(1 row)
SELECT * FROM order_id_seq;
last_value | log_cnt | is_called
------------+---------+-----------
2 | 32 | t
(1 row)
-
シーケンスの値の再設定
SELECT setval('order_id_seq', 0);
2019-02-10 11:29:35.823 UTC [43] ERROR: setval: value 0 is out of bounds for sequence "order_id_seq" (1..9223372036854775807)
2019-02-10 11:29:35.823 UTC [43] STATEMENT: SELECT setval('order_id_seq', 0);
ERROR: setval: value 0 is out of bounds for sequence "order_id_seq" (1..9223372036854775807)
SELECT setval('order_id_seq', 1);
setval
--------
1
(1 row)
シーケンスをSQL文で使用
SELECT setval('order_id_seq',
(SELECT max(order_id)
FROM orders)
);
setval
--------
5
(1 row)
INSERT
INTO orders(order_id, order_date, customer_id, prod_id, qty)
VALUES (nextval('order_id_seq'), now(), 2, 4, 7);
SELECT * FROM orders;
order_id | order_date | customer_id | prod_id | qty
----------+----------------------------+-------------+---------+-----
1 | 2019-01-28 23:04:57.664912 | 1 | 1 | 10
2 | 2019-01-28 23:04:57.689655 | 2 | 2 | 5
3 | 2019-01-28 23:04:57.714919 | 3 | 3 | 8
4 | 2019-01-28 23:04:57.742378 | 2 | 1 | 3
5 | 2019-01-28 23:04:57.764938 | 3 | 2 | 4
+ 6 | 2019-02-10 11:34:47.92838 | 2 | 4 | 7
(6 rows)
シーケンスと飛び番
-
「連番」であることの限界
- 外部キー制約等でSQLが失敗しても、nextval関数が実行されると連番が進んでしまう
- 削除すると飛び番が生ずる
INSERT
INTO orders(order_id, order_date, customer_id, prod_id, qty)
VALUES (nextval('order_id_seq'), now(), 999, 4, 7);
(略)
DETAIL: Key (customer_id)=(999) is not present in table "customer".
INSERT
INTO orders(order_id, order_date, customer_id, prod_id, qty)
VALUES (nextval('order_id_seq'), now(), 2, 4, 7);
-
7が飛ばされてしまった
order_id | order_date | customer_id | prod_id | qty ----------+----------------------------+-------------+---------+----- 1 | 2019-01-28 23:04:57.664912 | 1 | 1 | 10 2 | 2019-01-28 23:04:57.689655 | 2 | 2 | 5 3 | 2019-01-28 23:04:57.714919 | 3 | 3 | 8 4 | 2019-01-28 23:04:57.742378 | 2 | 1 | 3 5 | 2019-01-28 23:04:57.764938 | 3 | 2 | 4 6 | 2019-02-10 11:34:47.92838 | 2 | 4 | 7
-
8 | 2019-02-10 11:38:15.010806 | 2 | 4 | 7 (7 rows)
-
【疑問点】rollbackすると連番は戻る??
- MySQLのAUTO_INCREMENTは戻らない