CONSTRAINT節は、CREATE TABLE 文やALTER TABLE 文に任意に記述できる構造です。制約とはデータが従わなければならない規則です。制約には必要に応じて名前を与えることができます。
列レベルの制約は表にある単一の列を参照します。列名を指定するわけではなりません。(ただしチェック制約は除きます。)この制約は対象の列を参照しています。
表レベルの制約は表にある一つあるいはそれ以上の列を参照します。表レベルの制約は適用する列の名前を参照します。表レベルのチェック制約は0以上の列を参照します。
この列の値はNULLにできないことを指定します。(この種類の制約には名前を与えることができません。)
列により表中の行が一意に識別されることを指定します。列の値はNOT NULLと定義されなければなりません。
列の値が一意でなければならないと指定します。NULLの値は不可です。
列の値が、参照先の主キー、一意キーあるいはNULLでなければならないことを指定します。
列の値の規則を指定します。
一つ以上の列により表の行が一意に識別されることを表します。NULLの値は不可です。
列群の値が一意であると指定します。列はNOT NULLと定義されなければなりません。
列群の値が参照先の主キー、一意列あるいはNULLでなければならないことを指定します。
表の値に適用される様々な規則を指定します。
列制約と表制約は同じ機能を持ちますが、何に対して指定できるかが違います。表制約では一つ以上の列に対して、PRIMARY KEY、UNIQUE、CHECK、FOREIGN KEY制約を定義できます。列レベルの制約では(チェック制約を除いて)、一つの列への参照しか定義できません。
主キーでは表の行を一意に指定する列の集合が定義されます。
主キー制約を定義しした場合、主キーに含まれる何れの列の値もNULLであってはなりません。つまり何れの列もNULL値をとることができなくなります。
あらかじめNOT NULLと定義されているなら、既存の列をALTER TABLE ADD PRIMARY KEY という文で主キーに加えることができます。NULLの値は不可です。もし列にNULL値があれば、システムは主キー制約の追加を行いません。詳細な情報はALTER TABLE 文を参照してください。
表にはPRIMARY KEY制約を一つまでしか定義できません。しかし複数のUNIQUE制約を定義することはできます。
外部キー制約により、データベースの参照整合性を強制することができます。外部キーとは他の表のキーを参照する列あるいは列群です。(時によってはあまり多いことではありませんが、同じ表を参照することもあります。)外部キーは参照先の主キー制約・外部キー制約に一致するデータ型を持つ列を持たなければなりません。
表レベルの外部キー制約では、表の列を指定して制約を定義します。このとき同じ列を一回より多く使うことはできません。
参照定義(参照される表の列の一覧です。)に列が定義される場合、それは参照先の表にある一意性制約あるいは主キー制約に対応していなければなりません。 もし表に一意性制約があるなら、参照定義では列の一覧を省くことができます。
もし参照定義にて列の一覧が指定されておらず、参照先の表に主キーも定義されていないならば、例外が挙がります。(この例外は、もし参照先の表に一意性制約しかないならば、その列の一覧を参照定義に含めなければならない、という意味です。)
有効になっている外部キー制約が定義された表に挿入や更新が行われるとき、Derbyはその行が外部キー制約を破っていないか知るために、参照先の表で対応する参照キーの値を調査します。 もし制約が守られていなければ、Derbyは例外を挙げて、挿入ないし更新を却下します。
参照されたキー(外部キーにより参照される、主キー制約あるいは一意制約)のある表の行を、更新または削除した場合、Derbyはそのキーを参照するすべての外部キー制約を調べて、行の削除や変更が制約違反を引き起こさないか調査します。 もし行への削除や変更が制約違反を引き起こすのであれば、変更や削除は許可されずDerbyは例外を挙げます。
Derbyが制約のチェックを行うのは文が実行される時です。トランザクションがコミットされる時ではありません。
UNIQUE、PRIMARY KEY、FOREIGN KEYの各制約は制約を強制あるいは支援するための索引を生成します。(これは支援索引と呼ばれることがあります。) UNIQUE制約とPRIMARY KEY制約は一意索引を生成します。FOREIGN KEY索引は非一意な索引を作成んします。したがって、列や列の集合にUNIQUE制約やPRIMARY KEY制約、FOREIGN KEY索引が定義されている場合は、パフォーマンス向上のための索引をそれらの列に作成する必要はありません。Derbyが代わりに作成してくれています。索引と制約も参照してください。
問い合わせを最適化される時にオプティマイザはこれらの索引を利用できます。(CREATE INDEX 文を参照してください。)またこれらの索引はシステムが自動的に生成した名前を持ちます。
支援索引はDROP INDEX文では破棄できません。制約を破棄するか、表を破棄する必要があります。
チェック制約は表の内容に関する様々な規則を指定するために利用することができます。チェック制約には(真偽式で記述される)検索条件を指定できます。表中の全行について、この検索条件は満たされていなければなりません。INSERTやUPDATEにより行が変更される時、検索条件は変更される行に対して適用されます。何れかのチェック制約に違反すると、文全体が中断されます。
チェック制約が列定義の一部として指定された場合、制約は同じ列のみ参照できます。 表の定義の一部として指定されたチェック制約は、CREATE TABLE文にて先立って定義された列を参照することができます。
外部キーの定義にて、動作(CASCADE、RESTRICT、SET NULLおよびNO ACTION)が適切に書かれたON DELETE節や/とON UPDATE節を記述できます。 これらの節は、表の主キーが更新ないし削除されたときに、外部キーの関係が損なわれないように、対応する外部キーを変更したり、操作を拒絶したりする事を記述します。
参照性の制約の定義を行うとき、併せて更新または削除の規則を定義できます。
更新の規則は、親あるいは依存する表の行が更新されたときに適用されます。指定できるのは、NO ACTIONあるいはRESTRICTです。
親表の主キーの値が更新され、更新の規則がRESTRICTであった場合、Derbyは依存する表の外部キー制約を調べます。もし依存表の何れかの行が外部キー制約違反となるなら、トランザクションは巻き戻されます。
もし更新の規則がNO ACTIONであったなら、Derbyは更新が全て終わった後からトリガが実行される前に、依存する表の外部キー制約をチェックします。もし何れかの行が外部キー制約違反であるなら、文の実行は却下されます。
依存する表にある列の値が変更され、その値が外部キーの一部であった場合、更新の規則は暗黙裡にNO ACTIONとなります。NO ACTIONであるということは、外部キーが空ではない値に更新された時、更新後の値は親表の主キーと一致しなければならないことを意味します。もし親表の主キーと一致しないのであれば、文は却下されます。
削除の規則は親表の行が削除され、その行に依存する行が参照性制約の依存表にある場合に適用されます。このとき依存表の行も削除されることがあり、これを親表の削除が依存表に伝播したといいます。もし依存表が親表でもあるならば、今度はそこに定義された動作がその依存表に適用されます。
指定できる値は、NO ACTION、RESTRICT、CASCADE、それとSET NULLです。SET NULLは外部キーの何れかの列がnullの値を持つことができる場合のみ指定できます。
もし削除の規則が、
NO ACTIONならDerbyは依存表の外部キー制約を、削除が全て終わった後からトリガが実行される前にチェックします。もし依存表の何れかの行が外部キー制約に違反するなら文は却下されます。
RESTRICTなら、Derbyは依存表の外部制約を調べ、依存表の何れかの行が外部キー制約に違反する場合、トランザクションを巻き戻します。
CASCADEなら、削除が依存表に伝播されます。(適用可能な伝播先の依存表にも伝播します。)
SET NULLなら、依存表の外部キーにて空にできる列の値が空になります。(この場合も、依存表に依存する表にある外部キーの空にできる列の値は空になります。)
表を親とする参照性の制約には、個々の削除の規則があります。そして適用される全ての削除の規則によって、削除処理の結果が決まります。 したがって依存先がRESTRICTやNO ACTIONの参照性制約をもつなら、行は削除できません。 同様に削除が伝播してゆく場合でも、伝播先の依存行の参照性制約にて削除の規則がRESTRICTやNO ACTIONで定義されていれば、行を削除できません。
-- 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文は一意性制約・主キー制約・外部キー制約の影響を受けます。 対象表にこれらの制約が追加されたり削除されたりした場合、これらの文は無効となります。