OSSDB標準教科書

6章データベース定義の応用

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は戻らない

勉強日記

チラ裏

OSSDB標準教科書6章データベース定義の応用

6章データベース定義の応用

主キー

主キーを指定する

主キーの動作を確認する

複数列からなる主キー

外部キー

参照整合性制約

外部キーを指定する

外部キーの動作を確認する

CREATE TABLE文で主キー、外部キーを設定する

主キー、外部キーは必要か？

【補】DEFERRABLE制約

正規化

NULLについて

NOT NULL制約

NULLの判定

NULLの集約関数での取り扱い

空文字

シーケンス

シーケンスの作成

シーケンスの操作

シーケンスをSQL文で使用

シーケンスと飛び番

6章 データベース定義の応用

主キー

主キーを指定する

主キーの動作を確認する

複数列からなる主キー

外部キー

参照整合性制約

外部キーを指定する

外部キーの動作を確認する

CREATE TABLE文で主キー、外部キーを設定する

主キー、外部キーは必要か？

【補】DEFERRABLE制約

正規化

NULLについて

NOT NULL制約

NULLの判定

NULLの集約関数での取り扱い

空文字

シーケンス

シーケンスの作成

シーケンスの操作

シーケンスをSQL文で使用

シーケンスと飛び番

6章データベース定義の応用