CONSTRAINT節は、CREATE TABLE 文やALTER TABLE 文に任意に記述できる構造です。制約とはデータが従わなければならない規則です。制約には必要に応じて名前を与えることができます。
制約は以下のいずれかです。
Column-level constraintsused in CREATE
TABLE statementConstraintscolumn-level列制約には以下があります。
- NOT NULL
この列の値はNULLにできないことを指定します。(この種類の制約には名前を与えることができません。)
- PRIMARY KEYPrimary keyscreatingadding
列により表中の行が一意に識別されることを指定します。列の値はNOT NULLと定義されなければなりません。
ALTER TABLEにより主キーを追加しようとして、空の値を持つ列をその主キーに含めようとした場合、エラーとなって主キーは追加されません。より詳細な情報は、を参照してください。
- UNIQUE
列の値が一意でなければならないと指定します。NULLの値は不可です。
- FOREIGN KEY
列の値が、参照先の主キー、一意キーあるいはNULLでなければならないことを指定します。
- CHECK
列の値の規則を指定します。
Table constraintsused in CREATE TABLE statementConstraintstable-level
表制約には以下があります。
- PRIMARY KEYPrimary keyscreatingadding
一つ以上の列により表の行が一意に識別されることを表します。NULLの値は不可です。
- UNIQUE
列群の値が一意であると指定します。列はNOT NULLと定義されなければなりません。
- FOREIGN KEY
列群の値が参照先の主キー、一意列あるいはNULLでなければならないことを指定します。
外部キーが複数の列から構成されている場合、何れかの列がNULLであるならキーはNULLと見なされます。NULLでない列にどのような値があっても、挿入することができます。
- CHECK
表の値に適用される様々な規則を指定します。
Constraintsdifferences between column-level
and table-level
列制約と表制約は同じ機能を持ちますが、何に対して指定できるかが違います。表制約では一つ以上の列に対して、PRIMARY KEY、UNIQUE、CHECK、FOREIGN KEY制約を定義できます。列レベルの制約では(チェック制約を除いて)、一つの列への参照しか定義できません。
構文
主キーと一意性制約
Primary
key constraintsUnique constraints
主キーでは表の行を一意に指定する列の集合が定義されます。
主キー制約を定義しした場合、主キーに含まれる何れの列の値もNULLであってはなりません。つまり何れの列もNULL値をとることができなくなります。
あらかじめNOT NULLと定義されているなら、既存の列をALTER
TABLE ADD PRIMARY KEY という文で主キーに加えることができます。NULLの値は不可です。もし列にNULL値があれば、システムは主キー制約の追加を行いません。詳細な情報はを参照してください。
表にはPRIMARY KEY制約を一つまでしか定義できません。しかし複数のUNIQUE制約を定義することはできます。
外部キー制約 Foreign keysReferential
integrityvia foreign keys
外部キー制約により、データベースの参照整合性を強制することができます。外部キーとは他の表のキーを参照する列あるいは列群です。(時によってはあまり多いことではありませんが、同じ表を参照することもあります。)外部キーは参照先の主キー制約・外部キー制約に一致するデータ型を持つ列を持たなければなりません。
表レベルの外部キー制約では、表の列を指定して制約を定義します。このとき同じ列を一回より多く使うことはできません。
参照定義(参照される表の列の一覧です。)に列が定義される場合、それは参照先の表にある一意性制約あるいは主キー制約に対応していなければなりません。
もし表に一意性制約があるなら、参照定義では列の一覧を省くことができます。
もし参照定義にて列の一覧が指定されておらず、参照先の表に主キーも定義されていないならば、例外が挙がります。(この例外は、もし参照先の表に一意性制約しかないならば、その列の一覧を参照定義に含めなければならない、という意味です。)
参照先の一意性制約あるいは主キー制約に一致する値があれば、外部キー制約は守られているものとされます。もし外部キー制約が複数の列で定義されており、いずれかの列がNULLであるなら、外部キーの値はNULLとみなされます。
SQL-92標準にて言及があるように、複数の列で定義された外部キー制約では、制約された列が参照先の列にない値をとることが可能な場合があります。この状況を回避するために外部キーの全列にはNOT NULL制約を定義するべきです。
外部キー制約とDML
有効になっている外部キー制約が定義された表に挿入や更新が行われるとき、はその行が外部キー制約を破っていないか知るために、参照先の表で対応する参照キーの値を調査します。
もし制約が守られていなければ、は例外を挙げて、挿入ないし更新を却下します。
参照されたキー(外部キーにより参照される、主キー制約あるいは一意制約)のある表の行を、更新または削除した場合、はそのキーを参照するすべての外部キー制約を調べて、行の削除や変更が制約違反を引き起こさないか調査します。
もし行への削除や変更が制約違反を引き起こすのであれば、変更や削除は許可されずは例外を挙げます。
が制約のチェックを行うのは文が実行される時です。トランザクションがコミットされる時ではありません。
支援する索引 Backing indexescreated
automatically to enforce primary key, unique, and foreign key constraintsIndexesautomatic
creation of for primary key, unique, and foreign key constraints
UNIQUE、PRIMARY KEY、FOREIGN KEYの各制約は制約を強制あるいは支援するための索引を生成します。(これは支援索引と呼ばれることがあります。)
UNIQUE制約とPRIMARY KEY制約は一意索引を生成します。FOREIGN KEY索引は非一意な索引を作成んします。したがって、列や列の集合にUNIQUE制約やPRIMARY KEY制約、FOREIGN KEY索引が定義されている場合は、パフォーマンス向上のための索引をそれらの列に作成する必要はありません。が代わりに作成してくれています。も参照してください。
問い合わせを最適化される時にオプティマイザはこれらの索引を利用できます。(を参照してください。)またこれらの索引はシステムが自動的に生成した名前を持ちます。
支援索引はDROP INDEX文では破棄できません。制約を破棄するか、表を破棄する必要があります。
チェック制約 Check constraints
チェック制約は表の内容に関する様々な規則を指定するために利用することができます。チェック制約には(真偽式で記述される)検索条件を指定できます。表中の全行について、この検索条件は満たされていなければなりません。INSERTやUPDATEにより行が変更される時、検索条件は変更される行に対して適用されます。何れかのチェック制約に違反すると、文全体が中断されます。
検索条件の要件
チェック制約が列定義の一部として指定された場合、制約は同じ列のみ参照できます。
表の定義の一部として指定されたチェック制約は、CREATE TABLE文にて先立って定義された列を参照することができます。
検索条件は同じ値に適用された場合、常に同じ値を返す必要があります。したがって、以下のいずれも検索条件には置くことができません。
- 動的なパラメータ (?)
- 日時関数 (CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP)
- 副問い合わせ
- ユーザー関数 (例えばUSER、SESSION_USER、CURRENT_USER)
参照先に従う動作 Referential actionsConstraint
clausereferential actions in
外部キーの定義にて、動作(CASCADE、RESTRICT、SET NULLおよびNO ACTION)が適切に書かれたON DELETE節や/とON UPDATE節を記述できます。
これらの節は、表の主キーが更新ないし削除されたときに、外部キーの関係が損なわれないように、対応する外部キーを変更したり、操作を拒絶したりする事を記述します。
参照性の制約の定義を行うとき、併せて更新または削除の規則を定義できます。
更新の規則は、親あるいは依存する表の行が更新されたときに適用されます。指定できるのは、NO ACTIONあるいはRESTRICTです。
親表の主キーの値が更新され、更新の規則がRESTRICTであった場合、は依存する表の外部キー制約を調べます。もし依存表の何れかの行が外部キー制約違反となるなら、トランザクションは巻き戻されます。
もし更新の規則がNO ACTIONであったなら、は更新が全て終わった後からトリガが実行される前に、依存する表の外部キー制約をチェックします。もし何れかの行が外部キー制約違反であるなら、文の実行は却下されます。
依存する表にある列の値が変更され、その値が外部キーの一部であった場合、更新の規則は暗黙裡にNO ACTIONとなります。NO ACTIONであるということは、外部キーが空ではない値に更新された時、更新後の値は親表の主キーと一致しなければならないことを意味します。もし親表の主キーと一致しないのであれば、文は却下されます。
削除の規則は親表の行が削除され、その行に依存する行が参照性制約の依存表にある場合に適用されます。このとき依存表の行も削除されることがあり、これを親表の削除が依存表に伝播したといいます。もし依存表が親表でもあるならば、今度はそこに定義された動作がその依存表に適用されます。
指定できる値は、NO ACTION、RESTRICT、CASCADE、それとSET NULLです。SET NULLは外部キーの何れかの列がnullの値を持つことができる場合のみ指定できます。
もし削除の規則が、
NO ACTIONならは依存表の外部キー制約を、削除が全て終わった後からトリガが実行される前にチェックします。もし依存表の何れかの行が外部キー制約に違反するなら文は却下されます。
RESTRICTなら、は依存表の外部制約を調べ、依存表の何れかの行が外部キー制約に違反する場合、トランザクションを巻き戻します。
CASCADEなら、削除が依存表に伝播されます。(適用可能な伝播先の依存表にも伝播します。)
SET NULLなら、依存表の外部キーにて空にできる列の値が空になります。(この場合も、依存表に依存する表にある外部キーの空にできる列の値は空になります。)
表を親とする参照性の制約には、個々の削除の規則があります。そして適用される全ての削除の規則によって、削除処理の結果が決まります。
したがって依存先がRESTRICTやNO ACTIONの参照性制約をもつなら、行は削除できません。
同様に削除が伝播してゆく場合でも、伝播先の依存行の参照性制約にて削除の規則がRESTRICTやNO ACTIONで定義されていれば、行を削除できません。
親の表から行を削除すると、ほかの表への影響があります。親表の削除により影響を受けるすべての表を、親表と削除の関係があるといいます。削除によりこれらの表の行に対して次のような影響が及びます。
- もし削除の規則がRESTRICTあるいはNO ACTIONであれば、依存表は処理に関連しますが、処理の影響は受けません。(つまりは表の値を検証しますが、削除はしません。)
- もし削除の規則がSET NULLであれば、親表の行が削除されたり、削除が伝播された場合、依存表の行の値は更新の対象となります。
- もし削除の規則がCASCADEであれば、親表にて削除が行われた場合、依存表の行も削除されます。
- もし依存表が親表でもある場合、ここに書かれた処理が今度はその依存表に行われます。
例
-- OUT_TRAY_PKという名前の列レベルの主キー制約:
CREATE TABLE SAMP.OUT_TRAY
(
SENT TIMESTAMP,
DESTINATION CHAR(8),
SUBJECT CHAR(64) NOT NULL CONSTRAINT OUT_TRAY_PK PRIMARY KEY,
NOTE_TEXT VARCHAR(3000)
);
-- 表レベルの主キー制約では二つの列を
-- キーの定義に記述できます。
CREATE TABLE SAMP.SCHED
(
CLASS_CODE CHAR(7) NOT NULL,
DAY SMALLINT NOT NULL,
STARTING TIME,
ENDING TIME,
PRIMARY KEY (CLASS_CODE, DAY)
);
-- 列レベルの制約を算術チェックのために使います。
-- 表レベルの制約を従業員への税金が賞与を超えないようにするため
-- 使います。
CREATE TABLE SAMP.EMP
(
EMPNO CHAR(6) NOT NULL CONSTRAINT EMP_PK PRIMARY KEY,
FIRSTNME CHAR(12) NOT NULL,
MIDINIT vARCHAR(12) NOT NULL,
LASTNAME VARCHAR(15) NOT NULL,
SALARY DECIMAL(9,2) CONSTRAINT SAL_CK CHECK (SALARY >= 10000),
BONUS DECIMAL(9,2),
TAX DECIMAL(9,2),
CONSTRAINT BONUS_CK CHECK (BONUS > TAX)
);
-- MEAL列が適切な略字のみとるよう、チェック制約を使います。
CREATE TABLE FLIGHTS
(
FLIGHT_ID CHAR(6) NOT NULL ,
SEGMENT_NUMBER INTEGER NOT NULL ,
ORIG_AIRPORT CHAR(3),
DEPART_TIME TIME,
DEST_AIRPORT CHAR(3),
ARRIVE_TIME TIME,
MEAL CHAR(1) CONSTRAINT MEAL_CONSTRAINT
CHECK (MEAL IN ('B', 'L', 'D', 'S')),
PRIMARY KEY (FLIGHT_ID, SEGMENT_NUMBER)
);
CREATE TABLE METROPOLITAN
(
HOTEL_ID INT NOT NULL CONSTRAINT HOTELS_PK PRIMARY KEY,
HOTEL_NAME VARCHAR(40) NOT NULL,
CITY_ID INT CONSTRAINT METRO_FK REFERENCES CITIES
);
-- 表レベルの主キー制約と表レベルの外部キー制約を指定して、
-- 表を作成します。
CREATE TABLE FLTAVAIL
(
FLIGHT_ID CHAR(6) NOT NULL,
SEGMENT_NUMBER INT NOT NULL,
FLIGHT_DATE DATE NOT NULL,
ECONOMY_SEATS_TAKEN INT,
BUSINESS_SEATS_TAKEN INT,
FIRSTCLASS_SEATS_TAKEN INT,
CONSTRAINT FLTAVAIL_PK PRIMARY KEY (FLIGHT_ID, SEGMENT_NUMBER),
CONSTRAINT FLTS_FK
FOREIGN KEY (FLIGHT_ID, SEGMENT_NUMBER)
REFERENCES Flights (FLIGHT_ID, SEGMENT_NUMBER)
);
-- 列に一意性制約を加える。
ALTER TABLE SAMP.PROJECT
ADD CONSTRAINT P_UC UNIQUE (PROJNAME);
-- 列レベルの外部キー制約を利用して、
-- city_id列がCities表の主キーを参照する表を作成する。
CREATE TABLE CONDOS
(
CONDO_ID INT NOT NULL CONSTRAINT hotels_PK PRIMARY KEY,
CONDO_NAME VARCHAR(40) NOT NULL,
CITY_ID INT CONSTRAINT city_foreign_key
REFERENCES Cities ON DELETE CASCADE ON UPDATE RESTRICT
);
文の依存追跡システム
INSERT文やUPDATE文は対象表にある全制約の影響を受けます。
DELETE文は一意性制約・主キー制約・外部キー制約の影響を受けます。
対象表にこれらの制約が追加されたり削除されたりした場合、これらの文は無効となります。