一覧DB-01-1 · 表とデータモデル

データベースのキーと制約

利用者表へ行を追加し、主キー・UNIQUE・NOT NULL・CHECK・型の制約がどの値を拒否するか試します。最後に受講表で二列を組み合わせたキーを作ります。SQLiteのSTRICT表を使い、NULLは文字列と見分けられるようNULLと表示します。

説明

リレーショナルデータベースでは、一つの対象についての値を行にまとめ、各属性を列に保存します。どの行かを一意に指定するキーと、保存してよい値を定める制約は、重複や不整合を防ぐための基本になります。

利用者表へ行を追加し、主キー・UNIQUE・NOT NULL・CHECK・型の制約がどの値を拒否するか試します。最後に受講表で二列を組み合わせたキーを作ります。SQLiteのSTRICT表を使い、NULLは文字列と見分けられるようNULLと表示します。

1利用者表の列と制約

code・email・age・nicknameを持つ表を作り、定義を表示します。

CREATE TABLE users(code TEXT PRIMARY KEYcodeを主キーにする, email TEXT NOT NULL UNIQUEemailを必須・一意にする, age INTEGER CHECK(age>=0)年齢の範囲条件, nickname TEXT UNIQUE) STRICT表の型制約を有効にする; .schema users

codeを主キーに選び、emailにも非NULLかつ一意という条件を付けます。行を最小の属性組で一意に識別できるものを候補キーと呼び、その中から主キーを選びます。この設計ではcodeとemailがそれぞれ候補になり、ageには負数を許しません。

2二行を追加する

二人分の値を追加し、codeの順に取得します。

INSERT INTO users VALUES二行を追加('u1','a@example.test',20,NULL値がない状態),('u2','b@example.test',NULL,NULL); SELECT code,email,age,nickname FROM users ORDER BY code表示する順序を明示;

一行は一人の利用者を表します。u2の年齢はNULLで、0や空文字とは異なります。NULLが「不明」「未登録」など何を意味するかは列の設計で決めます。この表ではageもnicknameもNULLを許しています。

3保存された値の型

typeofで各値の保存型を確認します。

SELECT code,typeof(code)codeの保存型 AS code_type,age,typeof(age)ageの保存型 AS age_type FROM users ORDER BY code;

codeはtext、20はinteger、NULLはnullです。列に指定した型と、その行で値があるかどうかを分けて確認できます。NULLを許す列では、型がINTEGERでも全行に整数値が入るとは限りません。

4主キーが重複する行

既存のu1と同じcodeで追加を試します。

INSERT INTO users VALUES('u1'既存行と同じ主キー,'c@example.test',30,'c');

codeの一意性に違反して拒否されます。emailや他の値が違っていても、主キーが同じなら別の行として追加できません。

5別の候補キーが重複する行

codeをu3にし、emailだけ既存行と同じにします。

INSERT INTO users VALUES('u3'新しい主キー,'a@example.test'既存行と同じemail,30,'c');

今度はusers.emailの一意性違反です。主キーだけでなくUNIQUEでも重複を防げます。一意性の対象は、どの列・列の組合せへ制約を付けたかで決まります。

6STRICTテーブルの主キーにNULLを挿入する

codeをNULLにした行を追加します。

INSERT INTO users VALUES(NULL主キーへNULLを指定,'c@example.test',30,'c');

NOT NULLの違反として拒否されます。STRICT表の主キーには非NULLの条件も付きます。ただしSQLiteのINTEGER PRIMARY KEYはrowidの別名となり、NULLを渡すと番号を生成する特殊な動作があるため、このTEXTの主キーとは分けて考えます。

7範囲外の年齢

ageへ-1を入れます。

INSERT INTO users VALUES('u3','c@example.test',-1範囲外の値,'c');

CHECK(age>=0)に違反します。なお、u2のNULLはこのCHECKで拒否されません。CHECKの式がNULLになる場合は違反と扱わないため、値を必須にしたいならNOT NULLも指定します。

8整数へ変換できない値

ageへ文字列twentyを入れます。

INSERT INTO users VALUES('u3','c@example.test','twenty'整数へ変換できない文字列,'c');

STRICT表のINTEGER列へ保存できず、型制約で拒否されます。SQLiteの通常表はより柔軟な型の扱いをするため、ここではSTRICTを明示しています。

9損失なく変換できる文字列

今度は文字列20を入れ、保存型を取得します。

INSERT INTO users VALUES('u3','c@example.test','20'整数へ変換できる文字列,'c'); SELECT code,age,typeof(age)保存後の型を確認 AS age_type FROM users WHERE code='u3';

文字列として渡しても、整数20へ損失なく変換できるため保存されます。STRICTは一切の型変換を禁止する指定ではありません。変換できるかと、変換後に制約を満たすかが関係します。

10UNIQUE列のNULLは複数行に置ける

nicknameがNULLの行を取得し、現在の全件数も確認します。

SELECT code,nickname FROM users WHERE nickname IS NULLNULLの行を抽出 ORDER BY code; SELECT COUNT(*)拒否後に残った件数 AS users_count FROM users;

nicknameのNULLは二行にあります。SQLiteのUNIQUEではNULL同士を重複として扱いません。したがって、NULLを許すUNIQUE列をそのまま「すべての行を識別できる候補キー」とは扱えません。拒否された追加は残らず、全件数は3です。

11二列を組み合わせた主キー

受講者と講座の組を一意にする表を作ります。

CREATE TABLE enrollment(learner TEXT,course TEXT,PRIMARY KEY(learner,course)二列の組を一意にする) STRICT; INSERT INTO enrollment VALUES('u1','sql'),('u1','web'),('u2','sql')各列に重複を含む三つの組; SELECT * FROM enrollment ORDER BY learner,course;

learnerだけでもcourseだけでも重複がありますが、二列の組は重複しません。この複合キーで「一人が同じ講座に一回登録する」という行の単位を表します。

12同じ組を重ねて登録する

u1とsqlの組をもう一度追加します。

INSERT INTO enrollment VALUES('u1','sql')既存と同じ組; SELECT COUNT(*)拒否後の件数 AS registrations FROM enrollment;

learnerとcourseの組の制約違反となり、件数は3のままです。主キーを決めるときには、列の名前だけでなく、一行が何の単位を表すかを先に定めます。

まとめ

候補キーから主キーを選び、行が表す単位を制約へ対応させます。

NULL・型変換・範囲条件は別の性質で、必要な制約を組み合わせます。

複合キーは複数列の組を一意にし、各列単独の重複とは区別します。

操作履歴と解説を振り返れます。

SQLite 3.54.0

1
CREATE TABLE users(code TEXT PRIMARY KEY, email TEXT NOT NULL UNIQUE, age INTEGER CHECK(age>=0), nickname TEXT UNIQUE) STRICT; .schema users
CREATE TABLE users(code TEXT PRIMARY KEY, email TEXT NOT NULL UNIQUE, age INTEGER CHECK(age>=0), nickname TEXT UNIQUE) STRICT;
2
INSERT INTO users VALUES('u1','a@example.test',20,NULL),('u2','b@example.test',NULL,NULL); SELECT code,email,age,nickname FROM users ORDER BY code;
╭──────┬────────────────┬──────┬──────────╮
│ code │     email      │ age  │ nickname │
╞══════╪════════════════╪══════╪══════════╡
│ u1   │ a@example.test │   20 │ NULL     │
│ u2   │ b@example.test │ NULL │ NULL     │
╰──────┴────────────────┴──────┴──────────╯
3
SELECT code,typeof(code) AS code_type,age,typeof(age) AS age_type FROM users ORDER BY code;
╭──────┬───────────┬──────┬──────────╮
│ code │ code_type │ age  │ age_type │
╞══════╪═══════════╪══════╪══════════╡
│ u1   │ text      │   20 │ integer  │
│ u2   │ text      │ NULL │ null     │
╰──────┴───────────┴──────┴──────────╯
4
INSERT INTO users VALUES('u1','c@example.test',30,'c');
Error: UNIQUE constraint failed: users.code
5
INSERT INTO users VALUES('u3','a@example.test',30,'c');
Error: UNIQUE constraint failed: users.email
6
INSERT INTO users VALUES(NULL,'c@example.test',30,'c');
Error: NOT NULL constraint failed: users.code
7
INSERT INTO users VALUES('u3','c@example.test',-1,'c');
Error: CHECK constraint failed: age>=0
8
INSERT INTO users VALUES('u3','c@example.test','twenty','c');
Error: cannot store TEXT value in INTEGER column users.age
9
INSERT INTO users VALUES('u3','c@example.test','20','c'); SELECT code,age,typeof(age) AS age_type FROM users WHERE code='u3';
╭──────┬─────┬──────────╮
│ code │ age │ age_type │
╞══════╪═════╪══════════╡
│ u3   │  20 │ integer  │
╰──────┴─────┴──────────╯
10
SELECT code,nickname FROM users WHERE nickname IS NULL ORDER BY code; SELECT COUNT(*) AS users_count FROM users;
╭──────┬──────────╮
│ code │ nickname │
╞══════╪══════════╡
│ u1   │ NULL     │
│ u2   │ NULL     │
╰──────┴──────────╯
╭─────────────╮
│ users_count │
╞═════════════╡
│           3 │
╰─────────────╯
11
CREATE TABLE enrollment(learner TEXT,course TEXT,PRIMARY KEY(learner,course)) STRICT; INSERT INTO enrollment VALUES('u1','sql'),('u1','web'),('u2','sql'); SELECT * FROM enrollment ORDER BY learner,course;
╭─────────┬────────╮
│ learner │ course │
╞═════════╪════════╡
│ u1      │ sql    │
│ u1      │ web    │
│ u2      │ sql    │
╰─────────┴────────╯
12
INSERT INTO enrollment VALUES('u1','sql'); SELECT COUNT(*) AS registrations FROM enrollment;
Error: UNIQUE constraint failed: enrollment.learner, enrollment.course
╭───────────────╮
│ registrations │
╞═══════════════╡
│             3 │
╰───────────────╯

演習完了