制約
「DB（データベース）」の単元で学習した制約を、SQL文で付与する方法を学んでいきます。
（☆制約とは・・表にデータを作成（挿入/更新）するときに条件に合わないデータを入力させないための仕組みです。
表や列に制約を掛けることで、データベース側でデータの整合性を取る事が可能になります。）
ここで扱う制約の一覧は以下
| 制約名 | 説明 |
|---|---|
| 主キー制約（PRIMARY KEY） | 一意制約とNOT NULL制約の複合 |
| 一意制約（UNIQUE） | 重複するフィールドを禁止する |
| NOT NULL制約（NOT NULL） | NULL値を禁止する |
| チェック制約（CHECK） | 入力できる値に一定の制約を設ける |
| 外部キー制約（REFERENCES） | 他のテーブルの主キーまたは一意キーを参照する制約 |
ここでは列ごとに付与する「列制約」について学んでいきます。
表自体に付与する「表制約」も存在しますので、興味がある場合は調べてみてください。
主キー制約（PRIMARY KEY）
基本的に、すべての表には主キーを設定します。表の主キーは表内のすべての行を一意に識別します。
設計書等では、PRIMARY KEYの頭文字を取ってPKと書くこともあります。
1つのカラムを主キーとする場合
CREATE TABLE sample_primary1 ( id INT PRIMARY KEY, name VARCHAR(10), tel VARCHAR(13) );
CREATE TABLE文内の主キーとしたいカラムの[カラム名 データ型名]の後ろに記載します。この書き方は複数カラムに対して行うとエラーになります。
下記の記載でも同様の結果になります。
CREATE TABLE sample_primary1( id INT, name VARCHAR(10), tel VARCHAR(10), PRIMARY KEY(id));
使用機会は少ないですが、後から主キーを付与することもできます。（CREATE文で付与し忘れた場合など）
ALTER TABLE SAMPLE_PRIMARY1 MODIFY id INT PRIMARY KEY;
2つ以上のカラムを主キーとする場合（複合主キー）
CREATE TABLE sample_primary2( id INT, name VARCHAR(10), tel VARCHAR(13), PRIMARY KEY(id, name));
通例として列の定義群の後に記載します。
上記の場合、id列とname列の両方をもって複合主キーとしており、例えばnameが同じでもidが異なれば、制約に合致しているということになります。
一意制約（UNIQUE）
一意制約がカラムに設定されると、重複した値を受け付けないように出来ます。
CREATE TABLE sample_unique1 ( id INT PRIMARY KEY, name VARCHAR(10) UNIQUE, tel VARCHAR(13) );
name列には重複した値をセットすることができません。NULL値に関してはOKです。
PRIMARY KEYと同様に、ALTER TABLE文で後から設定することもできます。（以降のカラムも同様）
NOT NULL制約（NOT NULL）
NULL値を受け付けないように設定できます。
CREATE TABLE sample_null1 ( id INT PRIMARY KEY, name VARCHAR(10) NOT NULL, tel VARCHAR(13) );
チェック制約（CHECK）
チェック制約により指定した条件外の値はセットできないように設定できます。
CREATE TABLE sample_check1 ( id INT PRIMARY KEY, name VARCHAR(10) , age INT CHECK(AGE BETWEEN 0 AND 130) );
上記の例だと、age列には0～130以外の値は保存できません。
外部キー制約（REFERENCES）
テーブル同士が関係性を持つことを「リレーション」と言い、関連するテーブルに相手のidを登録します。相手のidを登録することで紐づけます。
「個人情報」テーブルが持つmember_idには、membersテーブルのidの値が入ることを想定しており、このmember_idを外部キーと呼びます。
設計書等では、FOREIN KEYの頭文字を取ってFKと書くこともあります。
外部キーであることを設定し、関連するカラム同士の関係性を保つのが外部キー制約です。
下記のようなmembersテーブルがあるとします。
CREATE TABLE members ( id INT PRIMARY KEY, name VARCHAR(15) , password VARCHAR(100));
profilesテーブルに外部キー制約を付与するには下記のSQL文になります。（membersテーブルが先にないと設定できません。）
CREATE TABLE profiles ( id INT PRIMARY KEY, member_id INT REFERENCES members(id), address VARCHAR(100));
REFERENCESの後に 参照先のテーブル名(カラム名) を記載します。
上記の記載の場合、制約は以下のように作用します。
①profilesテーブルのmember_id列にはmembersテーブルのidに存在しない値は登録できない
②profilesテーブルのmember_id列から参照されている場合、membersテーブルのレコードが削除できない
member_id INT REFERENCES members(id) ON DELETE CASCADE
上記のように[ON DELETE CASCADE]オプションを足すと、以下のように挙動します。
②profilesテーブルのmember_id列から参照されていても、membersテーブルのレコードは削除できる。該当のprofilesテーブルのレコードも削除される。
member_id INT REFERENCES members(id) ON DELETE SET NULL
上記のように[ON DELETE SET NULL]オプションを足すと、以下のように挙動します。
②profilesテーブルのmember_id列から参照されていても、membersテーブルのレコードは削除できる。該当のprofilesテーブルのレコードは削除されないが、member_id列にはNULLがセットされる。
その他
DEFAULT
DEFAULTキーワードを使用して、列のデフォルト値（初期値）を設定できます。
CREATE TABLE sample_default1 ( id INT PRIMARY KEY, name VARCHAR(10) DEFAULT '名無し', tel VARCHAR(13) );
DEFAULTの後に初期値を記載します。
上記のように設定したテーブルに対し、
INSERT文やUPDATE文で、DEFAULT値を設定したカラムに挿入する値をDEFAULTと置くと、デフォルト値を登録させることが出来ます。
INSERT INTO sample_default1 VALUES( 1, DEFAULT , '090-0000-0000' );
ALTER TABLE文で作成済のテーブルの列にデフォルト値を設定することも出来ます。ただし既に登録済みのデータには適用されません。
ALTER TABLE sample_default1 MODIFY tel VARCHAR(13) DEFAULT '000-0000-0000';
カラムの自動採番（SEQUENCE）
一般に、他の多くのDBではAUTO_INCREMENTという属性があり、カラムの数値を自動採番させることが出来ます。
例えば、最新が2まで保存されていれば、次のINSERT文では3を自動で保存してくれる、と言った機能です。
主に主キーに設定します。1から始まり2,3…と自動増分していけば、「NULLでなく重複のない連番」に間違いなくできるからです。
ただし、OracleにはAUTO_INCREMENTは存在しません。代わりに活用できるものとしてSEQUENCE（シーケンス、順序）があります。
実装例は以下
CREATE SEQUENCE sample_sequence1_seq01 START WITH 1 INCREMENT BY 1;
CREATE TABLE sample_sequence1 ( id INT DEFAULT sample_sequence1_seq01.nextval, name VARCHAR(10), tel VARCHAR(13), PRIMARY KEY(id) );
手順としては①SEQUENCEオブジェクトを作成、②カラムのデフォルト値にSEQUENCEを設定 です。
①SEQUENCEオブジェクトを作成
この指定の場合、「START WITH 1 INCREMENT BY 1」とある通り、1から始まり1ずつ増える順序「sample_sequence1_seq01」を生成します。ここでは割愛しますが、初期値や増分から最大値、サイクルなども指定できます。なお、SEQUENCE名は上記の「テーブル名_seq～」のように、どのテーブルに対するSEQUENCEか分かるように名前を付けます。
②カラムのデフォルト値にSEQUENCEを設定
SEQUENCEを作成後、カラムのデフォルトとして紐づけることで設定できます。[SEQUENCE].nextvalで、SEQUENCEの次の値を示します。
作成したテーブルにINSERTする場合、自動増分の列の値は指定しない形で挿入します。
INSERT INTO sample_sequence1(name, tel) VALUES('tarou', '000-0000-0000');
(name, tel)の部分で挿入対象の列を指定し、VALUESの後ろに列に対応する値を書いています。
上記のようなINSERT文を実行すると、DB側でid列に値を自動的に登録してくれます。複数データ挿入してみると、自動で連番が増えていくのが確認できるでしょう。
ORACLE／CREATE SEQUENCE編 – オラクルちょこっとリファレンス
カラムの自動採番（IDENTITY）
Oracleの自動採番として、SEQUENCEをさらに簡単に実装する方法としてIDENTITY（アイデンティティ）があります。
CREATE TABLE sample_identity1 ( id INT GENERATED BY DEFAULT ON NULL AS IDENTITY, name VARCHAR(10), tel VARCHAR(13), PRIMARY KEY(id) );
「BY DEFAULT ON NULL」の部分はオプションで、ユーザーがNULL値を指定するか、値がNULLに評価されると、値が生成されます。
実行後のid列のDEFAULT値を確認すると、「”SAMPLE”.”ISEQ$$_78265″.nextval」のような文字列が登録されているはずです。”ISEQ$$_78265″はSEQUENCE名であり、SEQUENCEの作成、DEFAULTへの指定を内部的に行ってくれているということです。
作成したテーブルにINSERTする場合、id列にNULLを指定すれば自動採番されます。
INSERT INTO sample_identity1 VALUES( NULL, 'tarou', '000-0000-0000');
SQL Developerでの操作・確認
SQL Developerを使用することで、SQL文を書かなくても、テーブルの確認や編集が可能です。
列タブや制約タブから確認ができます。
また、編集ボタンを押すと、表の編集画面から内容の確認・編集ができます。。
SEQUENCEは、「順序」から確認・編集できます。
