OlacleDBAの資格の勉強ついでに制約について初心者向けにまとめています。
制約とは (constraint)
制約(constraint)とはテーブルに格納するデータの種類を限定するための方法です。
型だけではまかないきれない制御を制約を加えることで表に無効なデータが入ることを阻止します。
有名な制約には not null (nullを許容しない)、や UNIQUE (一意の値のみ許容)などがあります。詳しく見ていきましょう。
制約の種類
5つの表の列に対して定義できる制約の種類を見ていきましょう。
| 種類 | 説明 |
|---|---|
| NOT NULL制約 (NOT NULL constraint) | 列にnull (空白)を許容しない |
| 一意キー制約 (unique constraint) | 列に重複を許容しない(nullは許容、複数の列の場合もあり) |
| 主キー制約 (primary key constraint) | nullと重複を許容しない。 NOT NULL制約 + 一意キー制約 |
| 外部キー制約 (foreign key constraint) | 列の値が関連する表の一意または主キーの値と一致する。(複数の列の場合もあり) |
| 検査/チェック制約 (check constraint) | 列の値が指定された条件に当てはまる、当てはまらなければ許容しない。（複数の列の場合もあり） |
制約にはREF制約 (REF constraint)もありますが、少し特殊なので今回は飛ばします。(他の列を参照する時により詳細に指定できる)
制約をサンプルコードとともに解説
5つの制約をより掘り下げていきましょう。ALTER TABLEなどでも設定可能ですがCREATE TABLE句を元に書いていきます。
サンプルコード
実際に動きを見ながらのほうがわかりやすいと思いますので、まず外部キー制約を除く制約を含めたテーブルを作成します。（外部キー制約は2つテーブルが必要なので後ほどサンプルコードを作成します）
今回はOracle Live SQLを使ってテストします。
※ アカウントを作らないといけないので面倒ですが、エンジニアであればOracleのアカウントは持っていて損はないので作ってしまいましょう。
※以下サンプルコードは見やすいようにスペースを入れていますが、Live SQLではエラーとなるためテストする際は余計なスペースを消してRUNしていただければと思います。
create table sampleT (
countId VARCHAR2(20) PRIMARY KEY,
emp_Id VARCHAR2(20) NOT NULL,
mail_add VARCHAR2(20) UNIQUE,
budget NUMBER(5,0) CHECK( ‘budget’ <= 30000)
);
※カラムに対して制約事項を追加しています。最後に制約を書くだけなので簡単です。複数書くことも可能
INSERT INTO sampleT
VALUES(
‘0001’,
‘0001’,
‘test.com’,
25000
);
SELECT * FROM sampleT;
結果
| COUNTID | EMP_ID | MAIL_ADD | BUDGET |
|---|---|---|---|
| 0001 | 0001 | test.com | 25000 |
INSERT INTO sampleT の値にnullなどを入れて実験してみると入る値が何か理解しやすいかと思います。
以下実験用のサンプルコードです。
NOT NULL制約 実験サンプルコード
列にnull (空白)を許容しない NOT NULL制約ではnullをINSERTやUPDATEで許容しません。
INSERT INTO sampleT
VALUES(
‘0002’,
null, — NG cannot insert NULL
‘test2.com’,
25000
);
nullの箇所を’0002’などとするとINSERT可能です。
一意キー制約 実験サンプルコード
列に重複を許容しない一意キー制約ではすでにテーブルに情報を持っている値をINSERTやUPDATEで許容しません。(mail_addのtest.comはすでにテーブル内に存在しているのではじかれる)
INSERT INTO sampleT
VALUES(
‘0003’,
‘0003’,
‘test.com’, // NG unique constraint
25000
);
test.comをtest.com1などに変更すると一意の値となりINSERT可能です。
またnullは複数許容します。
主キー制約 実験サンプルコード
nullと重複を許容しない主キー制約は一意キー制約とNOT NULL制約の両方を持っています。
INSERT INTO sampleT
VALUES(
‘0001’, — NG unique constraint
‘0001’,
‘test3.com’,
25000
);
INSERT INTO sampleT
VALUES(
null, — NG cannot insert NULL
‘0001’,
‘test3.com’,
25000
);
nullでも重複の値でもINSERTやUPDATEで許容しません。またテーブル内に主キーは1つのみ制約としてつけられます。
検査/チェック制約 実験サンプルコード
検査/チェック制約は少し小難しい気もしますが、複雑な条件でなければ簡単に条件を指定できます。
今回は「budget NUMBER(5,0) CHECK( ‘budget’ <= 30000)」としているので、30,000以下の値しか許容していません。条件もかっこ内に書いただけです。
INSERT INTO sampleT
VALUES(
‘0004’,
‘0001’,
‘test4.com’,
35000 — NG check constraint
);
30,000以下なので-35000にするとINSERT可能です。
外部キー制約 サンプルコード
後回しにしていた外部キー制約についてです。こちらのテストにはもう一つテーブルを作成してみます。
親表となるすでに作成済みのsampleTのcountIdを参照した、外部キー制約を行います。
create table sampleF (
countId2 VARCHAR2(20) ,
constraint FK foreign key(countId2) references sampleT(countId)
);
※FKは管理ようの名前のようなものです。
外部キー制約 実験サンプルコード
列の値がsampleTの主キーであるcountIdに登録されていない値は許容しません。
INSERT INTO sampleF
VALUES(
‘0100’ — NG integrity constraint parent key not found
);
すでにsampleTに登録済みの’0001’であればINSERT可能です。
おわり
少し長くなりましたが、制約はそこまで難しくはなくテーブルを管理する上でとても便利なツールです。頑張って覚えようとしなくても使っているうちに何となく覚えていくと思います。
個人的には外部キーとチェック制約はあまり実務で使うことはなかったので良い勉強となりました。
