はじめに

ポートフォリオ作成時に頭を悩ませるDB設計。
保守性が高いDBを設計するために、学習した内容の忘備録としてまとめました。

制約とは？

テーブルを作成する時に、列定義の最後に制約をつけると「空のデータ（NULL）」や「重複するデータ」をDBに登録できないように制限することができる。

SQL文は合っているが、システムの意図としては間違ったSQL文を送ってしまうという人為的なミスを防ぐために付けられる。

5つの制約

押さえておきたい制約は下記の5つ。

家計簿を例に挙げて一つずつ解説していく。

1.NOT NULL制約

2.UNIQUE制約

3.CHECK制約

4.主キー制約（PRIMARY KEY制約）

5.外部キー制約（FOREIGN KEY制約）

家計簿テーブルの概要

今回はPostgreSQLを利用し、ターミナル上で「家計簿テーブル（HOUSEKEEPING_BOOK）」と「費目テーブル（ITEMS）」を作成する。

その際に下記の吹き出しに記述している制約を列定義につける。

【テーブル①】

HOUSEKEEPING_BOOK（家計簿テーブル）

【カラム】

・HB_DATE：日付

・HB_ITEMS_ID：費目ID（例：水道光熱費、固定費、給与etc...）

・HB_MEMO：メモ（例：「1月の給与」などのメモ用カラム）

・HB_INCOMES：入金額

・HB_EXPENSES：出金額

【テーブル②】

ITEMS（費目テーブル）

【カラム】

・ITEMS_ID：費目名に対応するID

・ITEMS_NAME：費目名

1.NOT NULL制約

データを挿入する際に値が入っていないとエラーが発生する。

制約の付け方は下記の通りで、カラム名の最後にNOT NULLと記述する。

-- 入力必須項目の日付、メモにNOT NULL制約をつける
CREATE TABLE HOUSEKEEPING_BOOK (
HB_DATE DATE NOT NULL,
HB_ITEMS_ID INTEGER,
HB_MEMO VARCHAR(100) DEFAULT '不明' NOT NULL,
HB_INCOMES INTEGER DEFAULT 0,
HB_EXPENSES INTEGER DEFAULT 0
);

文中にあるDEFAULTはわざわざ値を指定しなくても、自動的に挿入してくれる値のこと。

DEFAULTの後に任意の値を記述すればOK！

デフォルト値を指定していない場合は①のようにNOT NULL制約がかけられている列全てに値を指定しなければならないが、②のようにデフォルト値の指定がある場合はわざわざ記述する必要はなくスッキリしたコードになる。

デフォルト値の指定がない場合

-- 入力必須項目である「HB_MEMO」の記述が必要
INSERT INTO HOUSEKEEPING_BOOK (HB_DATE, HB_ITEMS_ID, HB_MEMO, HB_EXPENSES)
VALUES ('2021-12-01', 3, '不明', 3000);

デフォルト値の指定がある場合

-- 「HB_MEMO」の記述がなくても自動的に「不明」が入るようになっている
INSERT INTO HOUSEKEEPING_BOOK (HB_DATE, HB_ITEMS_ID, HB_EXPENSES)
VALUES ('2021-12-01', 3, 3000);

2.UNIQUE制約

データの重複を防ぐ制約。

カラム名の最後にUNIQUEを付けることで制限することができる。

-- 登録済みの費目を入力できないように制限する
CREATE TABLE ITEMS (
ITEMS_ID INTEGER,
ITEMS_NAME VARCHAR(40) UNIQUE
);

この制約を付けることで、一度登録された費目名は再度登録できないようになっている。

-- 2回「食費」を登録するとエラーが発生する
INSERT INTO ITEMS VALUES (1, '食費');
INSERT INTO ITEMS VALUES (2, '食費'); 
ERROR:  duplicate key value violates unique constraint "items_items_name_key"
DETAIL:  Key (items_name)=(食費) already exists.

3.CHECK制約

()内に記述された条件をクリアした値でないとエラーが発生する。

カラム名の最後に**CHECK (条件式)**で制限をかけることができる。

-- 入金額と支出額にマイナスの値が入らないよう制限する
CREATE TABLE HOUSEKEEPING_BOOK (
HB_DATE DATE NOT NULL,
HB_ITEMS_ID INTEGER,
HB_MEMO VARCHAR(100) DEFAULT '不明' NOT NULL,
HB_INCOMES INTEGER DEFAULT 0 CHECK (HB_INCOMES >= 0),
HB_EXPENSES INTEGER DEFAULT 0 CHECK (HB_EXPENSES >= 0)
);

仮にマイナスの値を挿入してみると下記のエラーが発生する。

-- 支出に「-3000」と入力するとエラーが発生する
INSERT INTO HOUSEKEEPING_BOOK (HB_DATE, HB_ITEMS_ID, HB_EXPENSES)
VALUES ('2021-12-01', 3, -3000);
ERROR:  new row for relation "housekeeping_book" violates 
check constraint "housekeeping_book_hb_expenses_check"

4.主キー制約（PRIMARY KEY制約）

主キーとして扱いたいカラムにつける制約。

今までの制約は「これはできません！」という禁止型だったが、4と5の制約は「このカラムはこんな役割ですよ」と設定するような感覚。

カラム名の最後にPRIMARY KEYと付けることで主キーとして取り扱うことができる。

複数件主キーとして取り扱いたい場合、列定義の最後にまとめて記述する方法がある。

-- カラム名の最後に記述するパターン
CREATE TABLE ITEMS (
ITEMS_ID INTEGER PRIMARY KEY,
ITEMS_NAME VARCHAR(40) UNIQUE
);

-- 最後にまとめて記述するパターン（名前も主キーとして扱う）
CREATE TABLE ITEMS (
ITEMS_ID INTEGER,
ITEMS_NAME VARCHAR(40) UNIQUE,
PRIMARY KEY (ITEMS_ID, ITEMS_NAME)
);

主キーとは他と被らないデータかつNULLではないことが条件。

例えば、従業員番号のように他の人と被ることなく、その番号を指定することでその人のレコードを全て取得できる値であること。

5.外部キー制約（FOREIGN KEY制約）

データの更新や削除によって外部キーによるデータをうっかり変更しないようにつける制約のこと。

カラム名の最後に**REFERENCES 参照先のテーブル名(参照先名)**を記述する。

主キー制約同様、最後にまとめて記述する方法も可能。

-- 1つのカラムに外部キー制約を付ける
CREATE TABLE HOUSEKEEPING_BOOK (
HB_DATE DATE NOT NULL,
HB_ITEMS_ID INTEGER REFERENCES ITEMS(ITEMS_ID),
HB_MEMO VARCHAR(100) DEFAULT '不明' NOT NULL,
HB_INCOMES INTEGER DEFAULT 0 CHECK (HB_INCOMES >= 0),
HB_EXPENSES INTEGER DEFAULT 0 CHECK (HB_EXPENSES >= 0)
);

-- 複数のカラムに外部キー制約を付ける
CREATE TABLE HOUSEKEEPING_BOOK (
HB_DATE DATE NOT NULL,
HB_ITEMS_ID INTEGER,
HB_MEMO VARCHAR(100) DEFAULT '不明' NOT NULL,
HB_INCOMES INTEGER DEFAULT 0 CHECK (HB_INCOMES >= 0),
HB_EXPENSES INTEGER DEFAULT 0 CHECK (HB_EXPENSES >= 0),
FOREIGN KEY (HB_ITEMS_ID) REFERENCES ITEMS(ITEMS_ID) 
);

今回の場合、ITEMSテーブルを削除しようとすると下記のエラーが発生する。

DROP TABLE ITEMS;
ERROR:  cannot drop table items because other objects depend on it
DETAIL:  constraint housekeeping_book_hb_items_id_fkey 
on table housekeeping_book depends on table items
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

実践してみよう

（1）ITEMSテーブルの作成

※HOUSEKEEPING_BOOKテーブルに外部キー制約がかかっているため、先にIT EMSテーブルから作成する必要がある。

CREATE TABLE ITEMS (
ITEMS_ID INTEGER PRIMARY KEY,
ITEMS_NAME VARCHAR(40) UNIQUE
);

（2）HOUSEKEEPING_BOOKの作成

CREATE TABLE HOUSEKEEPING_BOOK (
HB_DATE DATE NOT NULL,
HB_ITEMS_ID INTEGER REFERENCES ITEMS(ITEMS_ID),
HB_MEMO VARCHAR(100) DEFAULT '不明' NOT NULL,
HB_INCOMES INTEGER DEFAULT 0 CHECK (HB_INCOMES >= 0),
HB_EXPENSES INTEGER DEFAULT 0 CHECK (HB_EXPENSES >= 0)
);

（3）それぞれのテーブルにデータを挿入

-- ITEMSテーブルに下記のデータを挿入
INSERT INTO ITEMS VALUES
(1, '給与'),
(2, '家賃'),
(3, '食費');

-- HOUSEKEEPING_BOOKテーブルに下記のデータを挿入
INSERT INTO HOUSEKEEPING_BOOK (HB_DATE, HB_ITEMS_ID, HB_EXPENSES)
VALUES ('2021-12-01', 3, 3000);
INSERT INTO HOUSEKEEPING_BOOK (HB_DATE, HB_ITEMS_ID, HB_INCOMES)
VALUES ('2021-12-25', 1, 200000);
INSERT INTO HOUSEKEEPING_BOOK (HB_DATE, HB_ITEMS_ID, HB_EXPENSES)
VALUES ('2021-12-27', 2, 100000);

（4）完成

最後に

少し文章が長くなってしまいましたが最後までご覧いただき有難うございました。
まだまだ学習不足なので、ここ間違っているななどあればご教示いただけると嬉しいです。

参考

スッキリわかるSQL入門（第10章：テーブルの作成）

https://www.amazon.co.jp/dp/B07L3MX199/ref=dp-kindle-redirect?_encoding=UTF8&btkr=1

https://qiita.com/SLEAZOIDS/items/d6fb9c2d131c3fdd1387

https://www.katalog.tokyo/?p=5403