| Oracle® Database開発ガイド 12c リリース1 (12.1) B71295-06 |
|
 前 |
 次 |
データベース・アプリケーションでは、データ整合性のメンテナンスは、アプリケーションで操作される表のデータが適切なビジネス・ルールに準拠する必要があることを意味します。ビジネス・ルールは、常に真または偽になる必要がある条件および関連を指定します。たとえば、従業員の給与が$100,000を超えないようにするビジネス・ルールや、EMPLOYEES表のすべての従業員がDEPARTMENTS表の部門に所属する必要があるビジネス・ルールなどがあります。各企業では、給与、従業員番号、在庫追跡などに関して独自のポリシーを定義しているため、企業ごとにビジネス・ルールが異なります。
『Oracle Database概要』で説明しているように、データ整合性を確認するには複数の方法があり、その1つは、可能なかぎり整合性制約(制約)を使用する方法です。
この章は、次の情報を補足します。
『Oracle Database概要』のデータ整合性および制約の説明
『Oracle Database管理者ガイド』の制約の管理に関する情報
『Oracle Database SQL言語リファレンス』の制約に関する構文およびセマンティクスの情報
内容は次のとおりです。
ビジネス・ルールは、可能なかぎり制約を使用して施行します。『Oracle Database概要』で説明されている利点に加えて、制約では高速という利点があります。Oracle Databaseでは、表の中のすべてのデータが制約に従っているかどうかをアプリケーション・コードよりも速くチェックできます。
例10-1では、部門表および従業員表を作成し、部門表のすべての値が一意であるというルールを施行する制約、および各従業員が有効な部門に所属する必要があるというルールを施行する制約を作成します。
例10-1 制約を使用したビジネス・ルールの施行
部門表を作成します。
DROP TABLE dept_tab; CREATE TABLE dept_tab ( deptname VARCHAR2(20), deptno INTEGER );
従業員表を作成します。
DROP TABLE emp_tab; CREATE TABLE emp_tab ( empname VARCHAR2(80), empno INTEGER, deptno INTEGER );
部門表のすべての値が一意であるというルールを施行する制約を作成します。
ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
各従業員が有効な部門に所属する必要があるというルールを施行する制約を作成します。
ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);
これで、emp_tabに従業員レコードを挿入すると、そのdeptnoの値がdept_tabに存在するかどうかがOracle Databaseによって確認されます。
各従業員が有効な部門に所属する必要があるというルールを施行する制約を使用せずに、dept_tabを問い合せるトリガーを使用して、emp_tabに挿入する従業員レコードのdeptnoの値が含まれていることを確認するとします。問合せでは読取り一貫性(CR)が使用されるため、他のトランザクションからのコミットされていない変更を見落とす場合があります。トリガーを使用したビジネス・ルールの施行の詳細は、『Oracle Database概要』を参照してください。
|
参照: 制約に関する構文およびセマンティクスの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
データ値が無効であることを表を問い合せずにアプリケーション・コードで判断できる場合は、制約とアプリケーション・コードの両方を使用してビジネス・ルールを施行することをお薦めします。アプリケーション・コードには、無効なデータを表に挿入できないようにすることで、ユーザーに即座にフィードバックして負担を減らすことができます。
例10-2では、例10-1が実行され、この列が表emp_tabに追加されたことを前提としています。
empgender VARCHAR2(1)
empgenderで有効な唯一の値は'M'および'F'です。あるユーザーが行をemp_tabに挿入またはemp_tab.empgenderの値を更新しようとした場合は、emp_tab.empgenderの新しい値が有効かどうかを、表を問い合せずにアプリケーション・コードで判断できます。値が無効の場合は、例10-2のように、無効な値を挿入することなく、アプリケーション・コードでユーザーに通知できます。
例10-2 制約とアプリケーション・コードの両方を使用したビジネス・ルールの施行
CREATE OR REPLACE PROCEDURE add_employee ( e_name emp_tab.empname%TYPE, e_gender emp_tab.empgender%TYPE, e_number emp_tab.empno%TYPE, e_dept emp_tab.deptno%TYPE ) AUTHID DEFINER IS BEGIN IF UPPER(e_gender) IN ('M','F') THEN INSERT INTO emp_tab VALUES (e_name, e_gender, e_number, e_dept); ELSE DBMS_OUTPUT.PUT_LINE('Gender must be M or F.'); END IF; END; / BEGIN add_employee ('Smith', 'H', 356, 20); END; /
結果:
Gender must be M or F.
一意キー制約または主キー制約が有効な場合は、Oracle Databaseによって索引が自動的に作成されますが、これらの索引を明示的に作成することをお薦めします。外部キー制約で索引を使用する場合は、索引を明示的に作成する必要があります。索引を明示的に作成する方法の詳細は、『Oracle Database管理者ガイド』または『Oracle Database SQL言語リファレンス』を参照してください。
制約で既存の索引を使用できる場合、その制約の索引はOracle Databaseでは作成されません。次のことに注意してください。
一意キー制約または主キー制約では、一意索引、一意でない索引全体、または一意でない索引の最初の数個の列のいずれかを使用できます。
一意キー制約または主キー制約で、一意でない索引を使用している場合は、その他の一意キー制約または主キー制約では、一意でないその索引を使用できません。
制約の列順序と索引は一致する必要がありません。
一意キー制約または主キー制約が使用している索引のオブジェクト番号は、その制約のCDEF$.ENABLEDに格納されています。この情報は、静的データ・ディクショナリ・ビューまたは動的なパフォーマンス・ビューには表示されません。
有効な一意キー制約または主キー制約が索引を使用している場合は、索引のみを削除することはできません。索引を削除するには、制約自身を削除するか、その制約を無効にしてから索引を削除する必要があります。制約の無効化および削除の詳細は、『Oracle Database管理者ガイド』を参照してください。
デフォルトでは、列にNULL値を含むことができます。列にNULL値が含まれていないことを確認するには、NOT NULL制約を使用します(『Oracle Database SQL言語リファレンス』を参照)。
列に非NULL値を含める必要がある場合。
たとえば、表HR.EMPLOYEESでは、各従業員に従業員IDが必要です。したがって、列HR.EMPLOYEES.EMPLOYEE_IDにはNOT NULL制約が含まれるため、EMPLOYEE_IDに非NULL値を指定せずに、新しい従業員レコードをHR.EMPLOYEESに挿入することはできません。給与を指定せずに、新しい従業員レコードをHR.EMPLOYEESに挿入できるため、列HR.EMPLOYEES.SALARYには、NOT NULL制約は含まれません。
表の索引スキャンを実行したり、すべての行の索引付けが必要な操作を実行できるようにする場合。
Oracle Databaseの索引には、値がすべてNULLのキーは格納されません。したがって、前述の種類の操作の場合は、1つ以上の索引列にNOT NULL制約が必要です。
例10-3は、SQL*PlusコマンドDESCRIBEを使用して、DEPARTMENTS表のどの列にNOT NULL制約があるか、NOT NULL制約がある列にNULL値を挿入しようとするとどうなるかを示します。
例10-3 NOT NULL制約がある列へのNULL値の挿入
DESCRIBE DEPARTMENTS;
結果:
Name Null? Type ----------------------------------------- -------- ------------ DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)
DEPARTMENT_ID列にNULLを挿入してみましょう。
INSERT INTO DEPARTMENTS ( DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID ) VALUES (NULL, 'Sales', 200, 1700);
結果:
VALUES (NULL, 'Sales', 200, 1700)
*
ERROR at line 4:
ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_ID")
NULLにできない列の値を省略することは、その列に値NULLを割り当てることと同じです。
INSERT INTO DEPARTMENTS (
DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID
)
VALUES ('Sales', 200, 1700);
結果:
INSERT INTO DEPARTMENTS (
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_ID")
DEPARTMENT_IDに非NULLのデフォルト値を指定することで、前述のエラーを回避できます。詳細は、10.5項「デフォルトの列値を使用する場合」を参照してください。
NOT NULL制約を他の制約と組み合せて、特定の列で可能な値をさらに制限できます。たとえば、NOT NULLと一意制約を組み合せると、一意キーに必ず値が入力され、新しい行のデータと既存の行のデータが競合する可能性がなくなります。詳細は、「外部キーに一意制約およびNOT NULL制約がある場合」を参照してください。
INSERT文(『Oracle Database SQL言語リファレンス』を参照)で特定の列の値を指定しない場合、その列はデフォルト値を受け取ります。デフォルトでは、そのデフォルト値はNULLです。列を定義する(CREATE TABLE文を使用、『Oracle Database SQL言語リファレンス』を参照)場合または列を変更する(ALTER TABLE文を使用、『Oracle Database SQL言語リファレンス』を参照)場合は、デフォルト値を変更できます。
|
注意: 列に非NULLのデフォルト値を指定しても、NOT NULL制約のように、列の値にNULL値が含まれないようにすることはできません。NOT NULL制約の詳細は、10.4項「NOT NULL制約を使用する場合」を参照してください。 |
デフォルトの列値は、次の状況で使用します。
列にNOT NULL制約がある場合。
列に非NULLのデフォルト値を指定すると、列に値を指定せずに行を挿入した場合に発生するエラーを防止できます。
列に最も一般的な値がある場合。
たとえば、社内のほとんどの部門がニューヨークにある場合は、列DEPARTMENTS.LOCATIONのデフォルト値を'NEW YORK'に設定します。
エントリなしを示す非NULL値がある場合。
たとえば、列EMPLOYEES.SALARYの値ゼロが、給与がまだ決定されていないことを意味する場合は、その列のデフォルト値をゼロに設定します。
エントリなしを示すデフォルトの列値ではテストを簡単にできます。たとえば、次のテストを変更できます。
IF (employees.salary IS NOT NULL) AND (employees.salary < 50000)
変更後のテスト:
IF employees.salary < 50000
表を変更するユーザーの名前を自動的に記録する場合。
たとえば、ビューを介してユーザーが行を表に挿入できるようにするとします。inserter列(ビューの定義に含める必要のない列)を実表に指定し、行を挿入したユーザーの名前を格納します。ユーザー名を自動的に記録するには、USERファンクションを起動するデフォルト値を定義します。次に例を示します。
CREATE TABLE audit_trail (
value1 NUMBER,
value2 VARCHAR2(32),
inserter VARCHAR2(30) DEFAULT USER);
表の主キーは各行を一意に識別し、行の重複も回避できます(通常はこの目的のみ)。したがって、主キーの値をNULLにすることはできません。
表に含めることができる主キーは最大で1つですが、複数の列を含めることができます(複合キーが可能)。主キーを指定するには、主キー制約を使用します。
実用的なものとしては、値が順次生成される単一列を主キーとして選択します。順序の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
主キーとして選択するもので、次に最適なのは、値が次のすべてに該当する単一列です。
一意
変更なし
非NULL
短い数値(入力が簡単)
値が長く順次生成できない複合主キーの使用については、最小限にします。
|
参照:
|
非NULL値の重複が許可されていない1つの列または複数の列の組合せ(主キーを除く)では、一意制約を使用します。次に例を示します。
| 一意キー | 主キー |
|---|---|
| 従業員の社会保障番号 | 従業員番号 |
| トラックのナンバープレートの番号 | トラック番号 |
| 顧客の電話番号(国コード列、市外局番列、市内局番列) | 顧客番号 |
| 部門名列および拠点列 | 部門番号 |
図10-1は、一意制約を使用した表、制約に違反している行、および制約を満たしている行を示しています。
|
参照:
|
2つの表に1つ以上の共通の列が含まれる場合、外部キー制約を使用して、参照整合性(両方の表で共通の列に常に同じ値が含まれるようにする)を施行できます。
|
注意: 外部キー制約は参照整合性制約とも呼ばれ、静的データ・ディクショナリ・ビュー*_CONSTRAINTSでは、そのCONSTRAINT_TYPEは、Rになります。 |
一方の表を参照表または親表として指定し、もう一方の表を依存または子表として指定します。親表では、共通する列に主キー制約または一意制約を定義します。子表では、共通する列に外部キー制約を定義します。これで、共通する列は外部キーで構成されます。外部キーに追加の制約を定義すると、親と子の関連に影響があります(詳細は、10.8.2項「親表と子表との関連の定義」を参照)。
図10-2に、部門番号に定義された外部キーを示します。この外部キーは、この列の値がそれぞれ部門表の主キーの値と一致することを保証します。制約によって、間違った部門番号が従業員表に入力される可能性を回避できます。
図10-2は、1つの列を共有する親表と子表、外部キー制約に違反している行、およびその制約を満たしている行を示しています。
内容は次のとおりです。
|
参照:
|
外部キーには、一致する主キーまたは一意キーがない場合でも、すべてのNULLのキー値を使用できます。
デフォルトでは(NOT NULL句またはCHECK句を指定しない場合)、外部キー制約は、ANSI/ISO規格の複合外部キーに対して不一致規則を施行します。
複合外部キーのNULL値に対する完全一致規則(キーのすべての構成要素がNULLまたは非NULLであることを要求する)を施行するには、すべての複合外部キーがNULLまたは非NULLであることのみを許可するCHECK制約を定義します。たとえば、列A、B、Cで構成される複合キーを次のとおり指定できます。
CHECK ((A IS NULL AND B IS NULL AND C IS NULL) OR
(A IS NOT NULL AND B IS NOT NULL AND C IS NOT NULL))
一般に、宣言型参照整合性を使用して、複合外部キーのNULL値に対する部分一致規則(キーの非NULL部分が、参照表の主キーまたは一意キー列の対応する部分にあることを要求する)を施行することはできません。この場合、『Oracle Database PL/SQL言語リファレンス』で説明するように、トリガーを使用して処理できることがよくあります。
親表と子表との関連のいくつかは、子表の外部キーで定義されている他のタイプの制約によって判断できます。
外部キーに他の制約がない場合 外部キーについて他の制約が定義されていない場合は、子表の行は何行でも同一の親キー値を参照できます。このモデルでは外部キーにNULLが許可されます。
このモデルは、外部キーに未定の値(NULL)を許可する親キーと外部キーとの間に1対多関連を確立します。employee(従業員)表とdepartment(部門)表の間のこのような関連の例を、図10-2に示します。各部門(親キー)には多数の従業員(外部キー)が所属しますが、一部の従業員は、部門に所属していない場合があります(外部キーでNULL)。
外部キーにNOT NULL制約がある場合 外部キーでNULLが許可されていない場合は、子表の各行は親キーの値を明示的に参照する必要があります。
子表の行は何行でも同一の親キー値を参照できるため、このモデルは、親キーと外部キーとの1対1関連を確立します。ただし、子表の各行は、必ず親キー値に対する参照を持っている必要があり、外部キーに値の欠如(NULL)があってはいけません。前述の項の例で、この関連を説明できます。ただし、このモデルでは、従業員は必ず特定の部門への参照を持つ必要があります。
外部キーに一意制約がある場合 外部キーで一意制約が定義されている場合は、子表の1つの行のみが指定された親キーの値を参照できます。このモデルでは外部キーにNULLが許可されます。
このモデルは、外部キーに未定の値(NULL)を許可する親キーと外部キーとの間に1対1関連を確立します。たとえば、従業員表に、企業の保険計画の従業員の会員番号を参照するMEMBERNOという名前の列があると想定します。また、INSURANCEという表には、MEMBERNOという主キーがあり、その他の列は保険証書に関連した各従業員の情報を保持しているとします。次の理由によって、従業員表のMEMBERNOは、外部キーかつ一意キーである必要があります。
EMP_TAB表およびINSURANCE表の参照整合性規則を施行するため(外部キー制約)
各従業員の会員番号を一意にするため(一意キー制約)
外部キーに一意制約およびNOT NULL制約がある場合 外部キーで一意制約およびNOT NULL制約が定義されている場合は、子表の1つの行のみが指定された親キーの値を参照できます。また、外部キーにNULL値が許可されないため、子表の各行は親キーの値を明示的に参照する必要があります。
このモデルは、外部キーに未定の値(NULL)を許可しない親キーと外部キーとの間に1対1関連を確立します。前述の例を拡張して、各従業員が一意の会員番号を持つように保証するとともに、従業員表のMEMBERNO列にNOT NULL制約を追加することで、従業員表のMEMBERNO列に未定義の値(NULL)が許可されないようにできます。
Oracle Databaseでは、1つの列を複数の外部キー制約で参照できます。依存キーの数に制限はありません。ある列が2つの異なる複合外部キーの一部になっている場合に、この状況が発生する可能性があります。
Oracle Databaseが制約を確認する場合に、制約が満たされないときは、エラーが表示されます。現行のトランザクションが終了するまで制約のチェックを遅延するには、SET CONSTRAINTS文を使用します。
|
注意: SET CONSTRAINTS文は、トリガー内では使用できません。 |
制約のチェックを遅延するには次のようにします。
データを適切に選択します。
データに次のいずれかの特性がある場合、一意キーおよび外部キーの制約チェックを遅延する必要があります。
表がスナップショットの場合
別のアプリケーションで処理される大量のデータを含む表であり、同じ順序でデータが戻されるかどうかわからない場合
外部キーに対するカスケード操作を更新します。
制約が遅延可能かどうかを確認します。
適切な表を指定した後、表の外部キー、一意キーおよび主キーがDEFERRABLEとして作成されているかどうかを確認します。
データを処理するアプリケーション内で、任意のデータを処理する前に、次のようにすべての制約に遅延を設定します。
SET CONSTRAINTS ALL DEFERRED;
(オプション)トランザクションをコミットする直前に制約違反をチェックします。
COMMIT文の直前に、SET CONSTRAINTS ALL IMMEDIATE文を実行します。制約に問題があった場合、この文は失敗し、エラーの原因になった制約が識別されます。制約が違反しているときにコミットした場合、トランザクションはロールバックされ、エラー・メッセージが表示されます。
例10-4では、表empの主キーと 外部キーがDEFERRABLEとして作成され、遅延されます。
例10-4 制約チェックの遅延
DROP TABLE dept; CREATE TABLE dept ( deptno NUMBER PRIMARY KEY, dname VARCHAR2 (30) ); DROP TABLE emp; CREATE TABLE emp ( empno NUMBER, ename VARCHAR2(30), deptno NUMBER, CONSTRAINT pk_emp_empno PRIMARY KEY (empno) DEFERRABLE, CONSTRAINT fk_emp_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno) DEFERRABLE ); INSERT INTO dept (deptno, dname) VALUES (10, 'Accounting'); INSERT INTO dept (deptno, dname) VALUES (20, 'SALES'); INSERT INTO emp (empno, ename, deptno) VALUES (1, 'Corleone', 10); INSERT INTO emp (empno, ename, deptno) VALUES (2, 'Costanza', 20); COMMIT; SET CONSTRAINTS ALL DEFERRED; UPDATE dept SET deptno = deptno + 10 WHERE deptno = 20;
問合せ:
SELECT * from dept ORDER BY deptno;
結果:
DEPTNO DNAME
---------- ------------------------------
10 Accounting
30 SALES
2 rows selected.
更新:
UPDATE emp SET deptno = deptno + 10 WHERE deptno = 20;
結果:
1 row updated.
問合せ:
SELECT * from emp ORDER BY deptno;
結果:
EMPNO ENAME DEPTNO
---------- ------------------------------ ----------
1 Corleone 10
2 Costanza 30
2 rows selected.
SET CONSTRAINTSは現行のトランザクションのみに適用されます。設定が継続するのは、そのトランザクションの間、または次のSET CONSTRAINTS文によってモードがリセットされるまでです。ALTER SESSION SET CONSTRAINTS文は、現行セッションにのみ適用されます。制約の作成時に指定したデフォルト値は、制約の存在中は維持されます。
|
参照: SET CONSTRAINTS文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
一意キーまたは主キーを作成すると、Oracle Databaseは、既存の索引が制約の一意性を強制するかどうかを確認します。既存の索引がない場合、データベースは索引を作成します。
Oracle Databaseが一意索引を使用して制約を施行し、それに対応付けられた制約が削除または使用禁止にされる場合、索引は削除されます。索引に関連付けられた統計を保持する場合(再作成には時間がかかる)は、DROP CONSTRAINT文にKEEP INDEX句を指定します。
使用可能な外部キーが主キーまたは一意キーを参照している間は、主キー制約または一意キー制約、または索引を使用禁止にしたり削除することはできません。
|
注意: 遅延可能制約を使用した一意キーおよび主キーには、非一意索引を使用してください。 |
一意キー制約および主キー制約の作成時に既存の索引を使用するには、CONSTRAINT句にUSING INDEXを含めます。詳細と例は、『Oracle Database SQL言語リファレンス』を参照してください。
一致する一意キーまたは主キーが決して更新または削除されない場合にかぎり、外部キーに索引を付けます。
|
参照: 外部キーの索引付けの詳細は、『Oracle Database概要』を参照してください。 |
参照制約の宣言では、リモート表の主キーまたは一意キーを参照する外部キーを指定できません。
ただし、トリガーを使用すると、複数のノードにまたがる親子の表の関連をメンテナンスできます。
|
参照: 参照整合性を施行するトリガーの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
|
注意: トリガーを使用して分散データベースの複数のノードにまたがる参照整合性を定義する場合、ネットワーク障害が親表および子表へのアクセスを制限する可能性があることに注意してください。たとえば、子表が 2つのデータベース間のネットワーク接続に障害が発生すると、参照整合性トリガーが |
比較などの論理式をベースとした整合性規則を施行する必要がある場合、CHECK制約を使用します。その他のタイプの制約で必要なチェックができる場合には、CHECK制約は使用しないでください。
CHECK制約の例を次に示します。
給与の値が10000を超えないように、従業員の給与にCHECK制約を定義します。
「BOSTON」、「NEW YORK」および「DALLAS」のみが許可されるように、部門の所在地にCHECK制約を定義します。
コミッションの額が給与より多くならないように、給与およびコミッションの列にCHECK制約を定義します。
CHECK制約では、条件は表のすべての行に対して真または不明である必要があります。条件が偽であると評価された場合、その文はロールバックされます。CHECK制約の条件には、次のような制限があります。
条件は、挿入または更新が行われている行の値を使用して評価できるブール式である必要があります。
条件に副問合せまたは順序を含めることはできません。
条件にSQLファンクションSYSDATE、UID、USERまたはUSERENVを含めることはできません。
条件に疑似列LEVELまたはROWNUMを含めることはできません。
条件にPRIOR演算子を含めることはできません。
条件にユーザー定義ファンクションを含めることはできません。
|
参照:
|
CHECK制約は、条件が偽であると評価される場合にのみCHECK制約に違反します。真および不明(NULLと比較して)はチェック条件には違反しません。したがって、定義するCHECK制約が、規則を施行するために十分明確であることを確認してください。
たとえば、次のCHECK制約について考えます。
CHECK (Sal > 0 OR Comm >= 0)
この規則は、「従業員の給与が0(ゼロ)より大きい場合、または従業員のコミッションが0(ゼロ)以上の場合は、従業員表の行を許可しない」と解釈されます。ただし、給与にNULLの値を持つ行は、チェック条件全体が不明であると評価されるため、そのコミッションの値の評価にかかわらず、CHECK制約に違反しません。このような場合には、SAL列とCOMM列の両方にNOT NULL制約を設定することによって、このような違反を回避できます。
|
注意: どのような場合にNULL条件の結果が不明な値となるかについては、『Oracle Database SQL言語リファレンス』の論理条件の真理値表を参照してください。 |
1つの列に、その定義で列を参照する複数のCHECK制約を指定できます。定義できるCHECK制約の数に制限はありません。
制約が評価される順序は定義されません。そのため、順序に依存したり、互いに競合するような複数の制約を定義しないでください。
ANSI/ISO規格によると、NOT NULL制約はCHECK制約の1つであり、その条件は次のとおりです。
CHECK (column_name IS NOT NULL)
このため、NOT NULL制約またはCHECK制約のいずれかを使用して、単一列に対するNOT NULL制約を記述できます。NOT NULL制約は、CHECK制約よりも簡単に使用できます。
複合キーがすべてNULLまたはすべて非NULL値を持つ場合は、CHECK制約を使用する必要があります。たとえば、次のCHECK制約を指定すると、列C1およびC2を構成する複合キーのキー値が、すべてNULLまたはすべて値を持つことができます。
CHECK ((C1 IS NULL AND C2 IS NULL) OR (C1 IS NOT NULL AND C2 IS NOT NULL))
例10-5および例10-6に、データベース設計のプロトタイプ・フェーズでの簡単な制約の作成方法を示します。これらの例では各制約に名前が付いています。制約に名前を付けると、データ定義言語(DDL)が複数回実行された場合に、システムが生成した異なる名前で、データベースが同じ制約の複数のコピーを作成することを回避できます。
例10-5では、CREATE TABLE文を使用して表と制約を同時に作成します。
例10-5 CREATE TABLE文を使用した制約の定義
DROP TABLE DeptTab; CREATE TABLE DeptTab ( Deptno NUMBER(3) CONSTRAINT pk_DeptTab_Deptno PRIMARY KEY, Dname VARCHAR2(15), Loc VARCHAR2(15), CONSTRAINT u_DeptTab_Dname_Loc UNIQUE (Dname, Loc), CONSTRAINT c_DeptTab_Loc CHECK (Loc IN ('NEW YORK', 'BOSTON', 'CHICAGO'))); DROP TABLE EmpTab; CREATE TABLE EmpTab ( Empno NUMBER(5) CONSTRAINT pk_EmpTab_Empno PRIMARY KEY, Ename VARCHAR2(15) NOT NULL, Job VARCHAR2(10), Mgr NUMBER(5) CONSTRAINT r_EmpTab_Mgr REFERENCES EmpTab, Hiredate DATE, Sal NUMBER(7,2), Comm NUMBER(5,2), Deptno NUMBER(3) NOT NULL CONSTRAINT r_EmpTab_DeptTab REFERENCES DeptTab ON DELETE CASCADE);
例10-6では、ALTER TABLE文を使用して既存の表に制約を作成します。
制約に違反する行が表に存在している場合、VALIDATED状態の制約は作成できません。
例10-6 ALTER TABLE文を使用した制約の定義
-- Create tables without constraints: DROP TABLE DeptTab; CREATE TABLE DeptTab ( Deptno NUMBER(3), Dname VARCHAR2(15), Loc VARCHAR2(15) ); DROP TABLE EmpTab; CREATE TABLE EmpTab ( Empno NUMBER(5), Ename VARCHAR2(15), Job VARCHAR2(10), Mgr NUMBER(5), Hiredate DATE, Sal NUMBER(7,2), Comm NUMBER(5,2), Deptno NUMBER(3) ); --Define constraints with the ALTER TABLE statement: ALTER TABLE DeptTab ADD CONSTRAINT pk_DeptTab_Deptno PRIMARY KEY (Deptno); ALTER TABLE EmpTab ADD CONSTRAINT fk_DeptTab_Deptno FOREIGN KEY (Deptno) REFERENCES DeptTab; ALTER TABLE EmpTab MODIFY (Ename VARCHAR2(15) NOT NULL);
|
参照: 大規模な本番データベースに対する制約の作成方法およびメンテナンス方法の詳細は、『Oracle Database管理者ガイド』を参照してください。 |
CREATE TABLEまたはCREATE ANY TABLEシステム権限がある場合、作成する表の制約を定義できます。
ALTER ANY TABLEシステム権限ある場合は、既存のすべての表に制約を定義できます。
特定の表のALTERオブジェクト権限がある場合は、その表に制約を定義できます。
一意キーおよび主キー制約では、表の所有者に、UNLIMITED TABLESPACEシステム権限、または対応する索引を含む表領域の割当て制限が必要です。
外部キー制約を定義できるのは、親表またはビューが自分のスキーマ内にある場合、または親表またはビューの参照キー列に対するREFERENCES権限を持っている場合です。詳細は、10.18.4項「外部キー制約の作成に必要な権限」を参照してください。
NOT NULL、一意キー、主キー、外部キーおよびCHECKの各制約に対して、制約句のCONSTRAINTオプションを使用して名前を割り当ててください。この名前は、そのユーザーが所有している他の制約名に対して一意である必要があります。制約名を指定しない場合、Oracle Databaseが名前を自動的に生成して割り当てます。
独自の名前を指定すると、制約違反のエラー・メッセージがよりわかりやすくなります。また、SQL文が複数回実行された場合に、異なる名前で重複した制約が作成されることを回避できます。
制約句のCONSTRAINTオプションの例として、前述のCREATE TABLE文およびALTER TABLE文の例を参照してください。データ・ディクショナリでは、各制約名が制約に関する他の情報に含まれます。
この項では、制約をユーザー自身で使用可能および使用禁止にするしくみ、および手順について説明します。
使用可能にされた制約。制約が使用可能な場合、対応する規則が対応付けられた列のデータ値に施行されます。制約の定義は、データ・ディクショナリ内に格納されます。
使用禁止にされた制約。制約が使用禁止の場合、それに対応する規則は施行されません。制約の定義は、データ・ディクショナリ内に格納されたままです。
整合性制約は、データベース内のデータに関するアサーションを表します。このアサーションは、制約を使用可能にすると必ず真になります。制約を使用禁止にすると、整合性制約に違反するデータがデータベース内に存在する可能性があるため、アサーションは真でない場合もあります。
内容は次のとおりです。
日常の操作では、制約を使用可能な状態にしておきます。特定の状況においては、パフォーマンス上の理由から、表の制約を一時的に使用禁止にする必要がある場合があります。次に例を示します。
SQL*Loaderを使用して、表に大量のデータをロードする場合
表に対して大規模な変更を行うバッチ作業を実施する場合(たとえば、既存の番号に1000を加えてすべての従業員番号を変更する場合)
表を1つずつインポートまたはエクスポートする場合
制約を一時的に使用禁止にすると、これらの操作が高速になります。
整合性制約を定義すると(CREATE TABLEまたはALTER TABLEを使用)、Oracle Databaseではデフォルトで制約が使用可能になります。コードをわかりやすくするために、例10-7のように、制約の定義にENABLE句を含めて制約を明示的に使用可能にできます。
例10-7 制約を使用可能にする
/* Use CREATE TABLE statement to create enabled constraint (ENABLE keyword is optional): */ DROP TABLE t1; CREATE TABLE t1 (Empno NUMBER(5) PRIMARY KEY ENABLE); /* Create table without constraint and then use ALTER TABLE statement to add enabled constraint (ENABLE keyword is optional): */ DROP TABLE t2; CREATE TABLE t2 (Empno NUMBER(5)); ALTER TABLE t2 ADD PRIMARY KEY (Empno) ENABLE;
個々のトランザクションによって一度に1つずつ行が移入される表の制約を定義する場合は、ENABLE句を指定します。こうすることで、データが常に一貫性を保つことが保証され、各DML文のパフォーマンス・オーバーヘッドが少なくなります。
整合性制約を使用可能にするALTER TABLE文は、表の既存の行がその整合性制約に違反するとエラーになります。文はロールバックされ、制約定義は格納されず使用可能にもなりません。
整合性制約を定義(CREATE TABLEまたはALTER TABLEを使用)して使用禁止にするには、例10-8のように定義にDISABLE句を指定します。
例10-8 制約を使用禁止にする
/* Use CREATE TABLE statement to create disabled constraint */ DROP TABLE t1; CREATE TABLE t1 (Empno NUMBER(5) PRIMARY KEY DISABLE); /* Create table without constraint and then use ALTER TABLE statement to add disabled constraint */ DROP TABLE t2; CREATE TABLE t2 (Empno NUMBER(5)); ALTER TABLE t2 ADD PRIMARY KEY (Empno) DISABLE;
ユーザーがアクセスする前に大容量のデータが挿入される表の制約を定義するときは、DISABLE句を指定します。特に、データの挿入後にデータをクリーン・アップしたり、空の列に順序番号または親子関係を入力する必要がある場合です。
制約を定義して使用禁止にするALTER TABLE文は、その規則が施行されていないため、エラーとなることはありません。
データをクリーン・アップし、空の列に値を入力した後で、データの挿入中に使用禁止にしていた制約を使用可能にできます。
既存の制約を使用可能にするには、例10-9のようにALTER TABLE文をENABLE句とともに使用します。
例10-9 既存の制約を使用可能にする
-- Create table with disabled constraints:
DROP TABLE DeptTab;
CREATE TABLE DeptTab (
Deptno NUMBER(3) PRIMARY KEY DISABLE,
Dname VARCHAR2(15),
Loc VARCHAR2(15),
CONSTRAINT uk_DeptTab_Dname_Loc UNIQUE (Dname, Loc) DISABLE,
CONSTRAINT c_DeptTab_Loc
CHECK (Loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')) DISABLE
);
-- Enable constraints:
ALTER TABLE DeptTab
ENABLE PRIMARY KEY
ENABLE CONSTRAINT uk_DeptTab_Dname_Loc
ENABLE CONSTRAINT c_DeptTab_Loc;
整合性制約を使用可能にするALTER TABLE文は、表の行がその整合性制約に違反するとエラーになります。この場合、文はロールバックされ、制約は使用可能になりません。
データが含まれている表に対して、大規模な挿入または更新を実行する必要がある場合は、整合性制約を一時的に使用禁止にして、バルク操作のパフォーマンスを向上させることができます。
既存の制約を使用禁止にするには、例10-10のようにALTER TABLE文をDISABLE句とともに使用します。
例10-10 既存の制約を使用禁止にする
-- Create table with enabled constraints:
DROP TABLE DeptTab;
CREATE TABLE DeptTab (
Deptno NUMBER(3) PRIMARY KEY ENABLE,
Dname VARCHAR2(15),
Loc VARCHAR2(15),
CONSTRAINT uk_DeptTab_Dname_Loc UNIQUE (Dname, Loc) ENABLE,
CONSTRAINT c_DeptTab_Loc
CHECK (Loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')) ENABLE
);
-- Disable constraints:
ALTER TABLE DeptTab
DISABLE PRIMARY KEY
DISABLE CONSTRAINT uk_DeptTab_Dname_Loc
DISABLE CONSTRAINT c_DeptTab_Loc;
一意キー、主キーおよび外部キーの各制約を使用可能または使用禁止にする場合は、いくつかの重要な問題および前提条件を認識してください。一意キー制約および主キー制約は、通常、データベース管理者が管理します。
表の行が整合性制約に違反する場合、この行は制約違反になり、制約に対する例外とされます。例外が存在する場合、制約を使用可能にはできません。制約に違反する行は、制約を使用可能にする前に更新または削除する必要があります。
制約を使用可能にするときに、特定の整合性制約に対する例外を指定できます。
制約を作成または使用可能にするときに、整合性制約の例外があるために文が正常に実行されなかった場合、文はロールバックされます。この場合、すべての例外が更新または削除されるまで制約を使用可能にできません。整合性制約に違反している行を判断するには、CREATE TABLE文またはALTER TABLE文のENABLE句にEXCEPTIONSオプションを指定します。
|
参照: 制約の例外に応答する場合は、『Oracle Database管理者ガイド』を参照してください。 |
Oracle8i以降では、例10-11のようにMODIFY CONSTRAINT句を使用して既存の制約状態を変更できます。
|
参照: 変更するパラメータの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
例10-11 制約の変更
/* Create & then modify a CHECK constraint: */ DROP TABLE X1Tab; CREATE TABLE X1Tab ( a1 NUMBER CONSTRAINT c_X1Tab_a1 CHECK (a1>3) DEFERRABLE DISABLE ); ALTER TABLE X1Tab MODIFY CONSTRAINT c_X1Tab_a1 ENABLE; ALTER TABLE X1Tab MODIFY CONSTRAINT c_X1Tab_a1 RELY; ALTER TABLE X1Tab MODIFY CONSTRAINT c_X1Tab_a1 INITIALLY DEFERRED; ALTER TABLE X1Tab MODIFY CONSTRAINT c_X1Tab_a1 ENABLE NOVALIDATE; /* Create & then modify a PRIMARY KEY constraint: */ DROP TABLE t1; CREATE TABLE t1 (a1 INT, b1 INT); ALTER TABLE t1 ADD CONSTRAINT pk_t1_a1 PRIMARY KEY(a1) DISABLE; ALTER TABLE t1 MODIFY PRIMARY KEY INITIALLY IMMEDIATE USING INDEX PCTFREE = 30 ENABLE NOVALIDATE; ALTER TABLE t1 MODIFY PRIMARY KEY ENABLE NOVALIDATE;
変更可能な制約のプロパティの1つは名前です。制約の名前を変更する状況には次のようなものがあります。
表と制約を複製する場合。
制約名は、複数のスキーマに対しても一意であることが必要です。このため、複製された表の制約を、元の表の制約と同じ名前にすることができません。
制約をデフォルトのシステム生成名で作成したが、使用可能または使用禁止の設定が容易になるように、覚えやすい制約名が必要な場合。
例10-12に、制約のシステム生成名を検索して変更する方法を示します。
例10-12 制約名の変更
DROP TABLE T; CREATE TABLE T ( C1 NUMBER PRIMARY KEY, C2 NUMBER );
問合せ:
SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'T' AND CONSTRAINT_TYPE = 'P';
結果(システムにより生成される制約名は様々です):
CONSTRAINT_NAME
------------------------------
SYS_C0013059
1 row selected.
この問合せで報告された制約名をT_C1_PKに変更します。
ALTER TABLE T RENAME CONSTRAINT SYS_C0013059 TO T_C1_PK;
問合せ:
SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'T' AND CONSTRAINT_TYPE = 'P';
結果:
CONSTRAINT_NAME ------------------------------ T_C1_PK 1 row selected.
制約を削除するには、ALTER TABLE文のDROP句を使用します。制約を削除する状況には次のようなものがあります。
制約で施行されるルールが適切でなくなった場合。
制約が不要になった場合。
1つの制約と、その制約が依存している他のすべての整合性制約を削除するには、CASCADEを指定します。
例10-13 制約の削除
-- Create table with constraints:
DROP TABLE DeptTab;
CREATE TABLE DeptTab (
Deptno NUMBER(3) PRIMARY KEY,
Dname VARCHAR2(15),
Loc VARCHAR2(15),
CONSTRAINT uk_DeptTab_Dname_Loc UNIQUE (Dname, Loc),
CONSTRAINT c_DeptTab_Loc
CHECK (Loc IN ('NEW YORK', 'BOSTON', 'CHICAGO'))
);
-- Drop constraints:
ALTER TABLE DeptTab
DROP PRIMARY KEY
DROP CONSTRAINT uk_DeptTab_Dname_Loc
DROP CONSTRAINT c_DeptTab_Loc;
一意キー、主キーおよび外部キーの各制約を削除する場合は、いくつかの重要な問題および前提条件を認識してください。一意キー制約および主キー制約は、通常、データベース管理者が管理します。
|
参照:
|
外部キー制約は、異なる表の列同士の関連を規定します。したがって、参照中の主キーまたは一意キーの制約が存在していないか、または使用可能になっていない場合は、制約を使用可能にできません。
外部キー制約(単一列または複合列)を定義するときに、REFERENCESオプションに列リストが指定されていないと、Oracle Databaseは、指定した表の主キーが参照されるものとみなします。または、カッコの中に親表で参照する列を明示的に指定できます。この列リストが親表の主キーまたは一意キーを参照するかどうかは、Oracle Databaseによって自動的に確認されます。参照していない場合は、エラー情報が戻されます。
外部キー制約を作成するには、制約の作成者に親表および子表に対するアクセス権限が必要です。
親表 参照整合性制約の作成者は、親表を所有するか、または親表の親キーを構成する列に対するREFERENCESオブジェクト権限が必要です。
子表 参照整合性制約の作成者は、表を作成する権限(CREATE TABLEまたはCREATE ANY TABLEシステム権限)または子表を変更する権限(子表のALTERオブジェクト権限またはALTER ANY TABLEシステム権限)が必要です。
どちらの場合も、必要な権限をロールを介して取得することはできません。権限は、明示的に制約の作成者に付与する必要があります。
これらの制限によって、次のことが可能となります。
子表の所有者は、施行される制約の種類、または制約を作成できる他のユーザーを、明示的に決定できます。
親表の所有者は、外部キーが所有者自身の表の主キーおよび一意キーに依存可能であるかどうかを明示的に決定できます。
Oracle Databaseでは、外部キー制約の定義の指定どおりに、異なるタイプの参照整合性アクションを施行できます。
親キーの削除または更新の回避 このデフォルト設定を指定すると、キーを参照する行が子表内にある場合、親キーが削除または更新されることはありません。次に例を示します。
CREATE TABLE Emp_tab ( FOREIGN KEY (Deptno) REFERENCES Dept_tab);
親キー削除時の子である行の削除 ON DELETE CASCADEアクションを指定すると、子表が参照する親キーのデータを削除できます。ただし、更新はできません。親キー内のデータが削除されると、削除された親キー値に依存する子表のすべての行も削除されます。この参照アクションを指定するには、外部キー制約の定義にON DELETE CASCADEオプションを指定します。次に例を示します。
CREATE TABLE Emp_tab ( FOREIGN KEY (Deptno) REFERENCES Dept_tab ON DELETE CASCADE);
親キー削除時の外部キーへのNULL設定 ON DELETE SET NULLアクションを指定すると、親キーを参照するデータを削除できます。ただし、更新はできません。親キー内の参照データが削除されると、削除された親キー値に依存する子表内のすべての行の外部キーがNULLに設定されます。この参照アクションを指定するには、外部キー制約の定義にON DELETE SET NULLオプションを指定します。次に例を示します。
CREATE TABLE Emp_tab ( FOREIGN KEY (Deptno) REFERENCES Dept_tab ON DELETE SET NULL);
制約の名前、影響がある列および制約の管理に有効な他の情報を検索するには、例10-14のように静的データ・ディクショナリ・ビュー*_CONSTRAINTSおよび*_CONS_COLUMNSに問い合せます。
|
参照: *_CONSTRAINTSおよび*_CONS_COLUMNSの詳細は、『Oracle Databaseリファレンス』を参照してください。 |
例10-14 制約に関する情報の表示
DROP TABLE DeptTab;
CREATE TABLE DeptTab (
Deptno NUMBER(3) PRIMARY KEY,
Dname VARCHAR2(15),
Loc VARCHAR2(15),
CONSTRAINT uk_DeptTab_Dname_Loc UNIQUE (Dname, Loc),
CONSTRAINT c_DeptTab_Loc
CHECK (Loc IN ('NEW YORK', 'BOSTON', 'CHICAGO'))
);
DROP TABLE EmpTab;
CREATE TABLE EmpTab (
Empno NUMBER(5) PRIMARY KEY,
Ename VARCHAR2(15) NOT NULL,
Job VARCHAR2(10),
Mgr NUMBER(5) CONSTRAINT r_EmpTab_Mgr
REFERENCES EmpTab ON DELETE CASCADE,
Hiredate DATE,
Sal NUMBER(7,2),
Comm NUMBER(5,2),
Deptno NUMBER(3) NOT NULL
CONSTRAINT r_EmpTab_Deptno REFERENCES DeptTab
);
-- Format columns (optional):
COLUMN CONSTRAINT_NAME FORMAT A20;
COLUMN CONSTRAINT_TYPE FORMAT A4 HEADING 'TYPE';
COLUMN TABLE_NAME FORMAT A10;
COLUMN R_CONSTRAINT_NAME FORMAT A17;
COLUMN SEARCH_CONDITION FORMAT A40;
COLUMN COLUMN_NAME FORMAT A12;
DeptTabおよびEmpTabでアクセス可能な制約をリスト:
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE (TABLE_NAME = 'DEPTTAB' OR TABLE_NAME = 'EMPTAB') ORDER BY CONSTRAINT_NAME;
結果:
CONSTRAINT_NAME TYPE TABLE_NAME R_CONSTRAINT_NAME -------------------- ---- ---------- ----------------- C_DEPTTAB_LOC C DEPTTAB R_EMPTAB_DEPTNO R EMPTAB SYS_C006286 R_EMPTAB_MGR R EMPTAB SYS_C006290 SYS_C006286 P DEPTTAB SYS_C006288 C EMPTAB SYS_C006289 C EMPTAB SYS_C006290 P EMPTAB UK_DEPTTAB_DNAME_LOC U DEPTTAB 8 rows selected.
DeptTabおよびEmpTabで、制約NOT NULLとCHECKを区別:
SELECT CONSTRAINT_NAME, SEARCH_CONDITION FROM USER_CONSTRAINTS WHERE (TABLE_NAME = 'DEPTTAB' OR TABLE_NAME = 'EMPTAB') AND CONSTRAINT_TYPE = 'C' ORDER BY CONSTRAINT_NAME;
結果:
CONSTRAINT_NAME SEARCH_CONDITION
-------------------- ----------------------------------------
C_DEPTTAB_LOC Loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')
SYS_C006288 "ENAME" IS NOT NULL
SYS_C006289 "DEPTNO" IS NOT NULL
3 rows selected.
DeptTabおよびEmpTabで、制約を構成する列をリスト:
SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME FROM USER_CONS_COLUMNS WHERE (TABLE_NAME = 'DEPTTAB' OR TABLE_NAME = 'EMPTAB') ORDER BY CONSTRAINT_NAME;
結果:
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME -------------------- ---------- ------------ C_DEPTTAB_LOC DEPTTAB LOC R_EMPTAB_DEPTNO EMPTAB DEPTNO R_EMPTAB_MGR EMPTAB MGR SYS_C006286 DEPTTAB DEPTNO SYS_C006288 EMPTAB ENAME SYS_C006289 EMPTAB DEPTNO SYS_C006290 EMPTAB EMPNO UK_DEPTTAB_DNAME_LOC DEPTTAB LOC UK_DEPTTAB_DNAME_LOC DEPTTAB DNAME 9 rows selected.
次のことに注意してください。
制約名には、ユーザー指定(たとえば、UK_DEPTTAB_DNAME_LOC)のものと、システム指定(たとえば、SYS_C006290)のものがあります。
各制約タイプは、CONSTRAINT_TYPE列に別々の文字で表示されます。次の表に、各制約タイプに対応する文字を示します。
| 制約タイプ | 文字 |
|---|---|
主キー |
P |
UNIQUEキー |
U |
外部キー |
R |
CHECK、NOT NULL |
C |
|
注意: その他の制約タイプで、CONSTRAINT_TYPE列に「V」の文字で表示されるものがあります。この制約タイプは、ビューに対するWITH CHECK OPTIONを使用して作成された制約に対応しています。 |
次の制約は、SEARCH_CONDITION列に明示的にリストされます。
NOT NULL制約
ユーザー定義CHECK制約の条件
