今回の記事では、実務ではなかなか見ることがないが情報処理技術者試験では出る可能性がある制約関連の文法を紹介したいと思います。
実務で良く見る制約としては、一意でなければならずNULL値も許可しない主キー制約(PRIMARY KEY制約)、値が一意でなければならない一意キー制約(UNIQUE制約)、NULL値を許可しないNOT NULL制約、過去の記事で紹介した外部キー制約(FOREIGN KEY制約)といった定義が存在します。
しかし、他にもデフォルト値や検査制約といった制約が存在するので、今回の記事で紹介します。
また、制約に関連する文法として、制約名付与、ドメイン作成、表明作成といった文法も存在するので、それも今回の記事で紹介します。
なお、ドメイン作成と表明作成はSQL92で標準化された比較的新しい文法なので、DBMS製品によってはそもそも実装されていない可能性があります。
（特に表明は超マイナーです。にも関わらずDBスペシャリストでは出題実績があります…。）
【その他の制約】
- デフォルト値（DEFAULT制約）
INSERT文でレコードを挿入する際、項目を指定しなかった場合は通常NULLが設定されるが、デフォルト値を設定しておくとそのデフォルト値が適用される。
（文法（列制約の場合））
カラム名 型 DEFAULT デフォルト値
（例）
create table 商品(
商品ID INT PRIMARY KEY,
商品名 CHAR(50) DEFAULT 'hoge'
);
↓
INSERT INTO 商品(商品ID) VALUES(1);
で、商品名に'hoge'が設定される。 - 検査制約（CHECK制約）
INSERT文やUPDATE文でカラムに値をセットする際、カラムにセット可能な値を指定し、その指定の範囲外の値をセットしようとした際に処理を失敗させる。
（文法（列制約の場合））
カラム名 型 CHECK (セット可能な値を示す式)
（例）
create table 商品(
商品ID INT PRIMARY KEY,
商品名 CHAR(50),
重量 INT CHECK(重量>0)
);
↓
INSERT文やUPDATE文で重量に0以下の値をセットしようとすると失敗する。
【制約に関連する文法】
- 制約名付与（CONSTRAINT句）
制約に任意の名前を付けることができる。
制約に名前を付けておくと、ALTER TABLEで制約を削除する際に、名前を指定して削除することも可能になる。
（文法（列制約の場合））
カラム名 型 CONSTRAINT 制約名 制約の記述
（例）
create table 商品(
商品ID INT PRIMARY KEY,
商品名 CHAR(50) CONSTRAINT 商品非NULL NOT NULL
);
↓
ALTER TABLE 商品 DROP CONSTRAINT 商品非NULL;
で制約削除ができる。
※制約名を指定しなくても「ALTER TABLE 商品 MODIFY (商品名 NULL);」で制約削除できる。 - ドメイン作成（CREATE DOMAIN）
独自にドメイン（INTやCHAR等と同じ扱いの型）を定義できる。
（文法）
CREATE DOMAIN ドメイン名 型の内容
（例）
CREATE DOMAIN 名前 CHAR(50)
create table 商品(
商品ID INT PRIMARY KEY,
商品名 名前
);
↓
商品名の型がCHAR(50)でテーブルが生成される。 - 表明作成（CREATE ASSERTION）
複数のテーブルに対して制約をかけることができる。
（文法）
CREATE ASSERTION 表明名 CHECK(セット可能な値を示す式)
（例）
create table 商品(
商品ID INT PRIMARY KEY,
商品名 CHAR(50),
価格 INT
);
create table 価格下限(
商品ID INT PRIMARY KEY,
価格下限 INT
);
CREATE ASSERTION 価格チェック CHECK
(NOT EXIST
(SELECT *
FROM 商品
JOIN 価格下限
ON (商品.商品ID=価格下限.商品ID)
WHERE 商品.価格 < 価格下限.価格下限
)
)
↓
価格下限を下回る価格の商品が登録されることがないかを検査している。
例えば、価格が80、価格下限が100の商品を商品テーブルに登録しようとするとエラーになる。
---------------------
目次
https://1drv.ms/b/s!AivF3bzWXOzuhG1Xk5hscKYqkLkM
ネタに困った時はSQLネタが良いですね。
よほど古いシステムかよほど新しいシステムでもない限りSQLはどこでも使いますし、私自身も扱いに慣れているので。
