12 データベース・アプリケーションにおけるデータ整合性のメンテナンス

データベース・アプリケーションでは、データ整合性のメンテナンスは、アプリケーションで操作される表のデータが適切なビジネス・ルールに準拠する必要があることを意味します。ビジネス・ルールは、常に真または偽になる必要がある条件および関連を指定します。たとえば、従業員の給与が$100,000を超えないようにするビジネス・ルールや、EMPLOYEES表のすべての従業員がDEPARTMENTS表の部門に所属する必要があるビジネス・ルールなどがあります。各企業では、給与、従業員番号、在庫追跡などに関して独自のポリシーを定義しているため、企業ごとにビジネス・ルールが異なります。

データ整合性を確認する方法は複数あり、可能なかぎり、整合性制約(または制約)による方法を使用する必要があります。

この章は、次の情報を補足します。

ノート:

この章は、表に対する制約のみに適用されます。ビューに対する制約では、データ整合性をメンテナンスしたり、関連する索引を持つことはできません。これらは、ビューを伴う問合せをリライトできるため、マテリアライズド・ビューおよびその他のデータ・ウェアハウス機能を使用した場合のパフォーマンスが向上します。

関連項目:

トピック:

12.1 制約を使用したビジネス・ルールの施行

ビジネス・ルールは、可能なかぎり制約を使用して施行します。制約には、速度の利点があります。Oracle Databaseは、表内のすべてのデータが制約に従っていることのチェックを、アプリケーション・コードで同等のチェックを行うよりも速く実行できます。

例12-1では、部門表および従業員表を作成し、部門表のすべての値が一意であるというルールを施行する制約、および各従業員が有効な部門に所属する必要があるというルールを施行する制約を作成します。

例12-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 SQL言語リファレンス』を参照してください。

  • 整合性制約の利点をすべて示すリストは、『Oracle Database概要』を参照してください。

  • トリガーの使用によるビジネス・ルールの強制の詳細は、『Oracle Database概要』を参照してください。

12.2 制約とアプリケーション・コードの両方を使用したビジネス・ルールの施行

データ値が無効であることを表を問い合せずにアプリケーション・コードで判断できる場合は、制約とアプリケーション・コードの両方を使用してビジネス・ルールを施行することをお薦めします。アプリケーション・コードには、無効なデータを表に挿入できないようにすることで、ユーザーに即座にフィードバックして負担を減らすことができます。

例12-2では、例12-1が実行され、この列が表emp_tabに追加されたことを前提としています。 

empgender VARCHAR2(1)

empgenderで有効な唯一の値は'M'および'F'です。あるユーザーが行をemp_tabに挿入またはemp_tab.empgenderの値を更新しようとした場合は、emp_tab.empgenderの新しい値が有効かどうかを、表を問い合せずにアプリケーション・コードで判断できます。値が無効の場合は、例12-2のように、無効な値を挿入することなく、アプリケーション・コードでユーザーに通知できます。

例12-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.

12.3 制約で使用する索引の作成

一意キー制約または主キー制約が有効な場合は、Oracle Databaseによって索引が自動的に作成されますが、これらの索引を明示的に作成することをお薦めします。外部キー制約で索引を使用する場合は、索引を明示的に作成する必要があります。

制約で既存の索引を使用できる場合、その制約の索引はOracle Databaseでは作成されません。次の点に注意してください。

  • 一意キー制約または主キー制約では、一意索引、一意でない索引全体、または一意でない索引の最初の数個の列のいずれかを使用できます。

  • 一意キー制約または主キー制約で、一意でない索引を使用している場合は、その他の一意キー制約または主キー制約では、一意でないその索引を使用できません。

  • 制約の列順序と索引は一致する必要がありません。

  • 一意キー制約または主キー制約が使用している索引のオブジェクト番号は、その制約のCDEF$.ENABLEDに格納されています。この情報は、静的データ・ディクショナリ・ビューまたは動的なパフォーマンス・ビューには表示されません。

    有効な一意キー制約または主キー制約が索引を使用している場合は、索引のみを削除することはできません。索引を削除するには、制約自身を削除するか、その制約を無効にしてから索引を削除する必要があります。

関連項目:

12.4 NOT NULL制約を使用する場合

デフォルトでは、列にNULL値を含むことができます。列にNULL値が含まれていないことを確認するには、NOT NULL制約を使用します。

NOT NULL制約は、次の状況の両方で使用します。

  • 列に非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制約が必要です。

例12-3は、SQL*PlusコマンドDESCRIBEを使用して、DEPARTMENTS表のどの列にNOT NULL制約があるか、NOT NULL制約がある列にNULL値を挿入しようとするとどうなるかを示します。

例12-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のデフォルト値を指定することで、前述のエラーを回避できます。

NOT NULL制約を他の制約と組み合せて、特定の列で可能な値をさらに制限できます。たとえば、NOT NULLと一意制約を組み合せると、一意キーに必ず値が入力され、新しい行のデータと既存の行のデータが競合する可能性がなくなります。

関連項目:

12.5 デフォルトの列値を使用する場合

INSERT文で特定の列の値を指定しない場合、その列はデフォルト値を受け取ります。デフォルトでは、そのデフォルト値はNULLです。表の作成中に列を定義するとき、またはALTER TABLE文を使用して列を変更するときに、デフォルト値を変更できます。

ノート:

列に非NULLのデフォルト値を指定しても、NOT NULL制約のように、列の値に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);

関連項目:

12.6 表の主キーの選択(PRIMARY KEY制約)

表の主キーは各行を一意に識別し、行の重複も回避できます(通常はこの目的のみ)。したがって、主キーの値をNULLにすることはできません。

表に含めることができる主キーは最大で1つですが、複数の列を含めることができます(複合キーが可能)。主キーを指定するには、主キー制約を使用します。

実用的なものとしては、値が順次生成される単一列を主キーとして選択します。

主キーとして選択するもので、次に最適なのは、値が次のすべてに該当する単一列です。

  • 一意

  • 変更なし

  • 非NULL

  • 短い数値(入力が簡単)

値が長く順次生成できない複合主キーの使用については、最小限にします。

関連項目:

  • 主キー制約の詳細は、『Oracle Database概要』を参照してください。

  • 制限などの主キー制約の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

  • 順序の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

12.7 UNIQUE制約を使用する場合

非NULL値の重複が許可されていない1つの列または複数の列の組合せ(主キーを除く)では、一意制約を使用します。たとえば:

一意キー 主キー

従業員の社会保障番号

従業員番号

トラックのナンバープレートの番号

トラック番号

顧客の電話番号(国コード列、市外局番列、市内局番列)

顧客番号

部門名列および拠点列

部門番号

図12-1は、UNIQUE制約を使用した表、制約に違反している行、および制約を満たしている行を示しています。

図12-1 UNIQUE制約に違反およびUNIQUE制約を満たす行

図12-1の説明が続きます
「図12-1 UNIQUE制約に違反およびUNIQUE制約を満たす行」の説明

関連項目:

  • 一意制約の詳細は、『Oracle Database概要』を参照してください。

  • 制限などの一意制約の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

12.8 FOREIGN KEY制約を使用した参照整合性の施行

2つの表に1つ以上の共通の列が含まれる場合、外部キー制約を使用して、参照整合性(両方の表で共通の列に常に同じ値が含まれるようにする)を施行できます。

ノート:

外部キー制約は参照整合性制約とも呼ばれ、静的データ・ディクショナリ・ビュー*_CONSTRAINTSでは、そのCONSTRAINT_TYPEは、Rになります。

一方の表を参照表または親表として指定し、もう一方の表を依存または子表として指定します。親表では、共通する列に主キー制約または一意制約を定義します。子表では、共通する列に外部キー制約を定義します。これで、共通する列は外部キーで構成されます。外部キーに追加で制約を定義すると、親子関係に影響します。

図12-2に、部門番号に定義された外部キーを示します。この外部キーは、この列の値がそれぞれ部門表の主キーの値と一致することを保証します。制約によって、間違った部門番号が従業員表に入力される可能性を回避できます。

図12-2は、1つの列を共有する親表と子表、外部キー制約に違反している行、およびその制約を満たしている行を示しています。

図12-2 FOREIGN KEY制約に違反および外部キー制約を満たす行

図12-2の説明が続きます
「図12-2 FOREIGN KEY制約に違反および外部キー制約を満たす行」の説明

トピック:

関連項目:

  • 外部キー制約の詳細は、『Oracle Database概要』を参照してください。

  • 制限などの外部キー制約の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

  • 親表と子表との関連の定義

12.8.1 FOREIGN KEY制約およびNULL値

外部キーには、一致する主キーまたは一意キーがない場合でも、すべての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言語リファレンス』を参照してください。

12.8.2 親表と子表との関連の定義

親表と子表との関連のいくつかは、子表の外部キーで定義されている他のタイプの制約によって判断できます。

外部キーに制約がありません

外部キーに他の制約が定義されていない場合は、子表の行は何行でも同一の親キー値を参照できます。このモデルでは外部キーにNULLが許可されます。

このモデルは、外部キーに未定の値(NULL)を許可する親キーと外部キーとの間に1対多関連を確立します。employee(従業員)表とdepartment(部門)表の間のこのような関連の例を、図12-2に示します。各部門(親キー)には多数の従業員(外部キー)が所属しますが、一部の従業員は、部門に所属していない場合があります(外部キーでNULL)。

外部キーのNOT NULL制約

外部キーにnullが許容されていない場合、子表の各行は親キーで値を明示的に参照する必要があります(外部キーにnullが許容されていないため)。

子表の行は何行でも同一の親キー値を参照できるため、このモデルは、親キーと外部キーとの1対1関連を確立します。ただし、子表の各行は、必ず親キー値に対する参照を持っている必要があり、外部キーに値の欠如(NULL)があってはいけません。前述の項の例で、この関連を説明できます。ただし、このモデルでは、従業員は必ず特定の部門への参照を持つ必要があります。

外部キーのUNIQUE制約

外部キーでUNIQUE制約が定義されている場合は、子表の1つの行のみが指定された親キーの値を参照できます。このモデルでは外部キーにNULLが許可されます。

このモデルは、外部キーに未定の値(NULL)を許可する親キーと外部キーとの間に1対1関連を確立します。たとえば、従業員表に、企業の保険計画の従業員の会員番号を参照するMEMBERNOという名前の列があると想定します。また、INSURANCEという表には、MEMBERNOという主キーがあり、その他の列は保険証書に関連した各従業員の情報を保持しているとします。次の理由によって、従業員表のMEMBERNOは、外部キーかつ一意キーである必要があります。

  • EMP_TAB表およびINSURANCE表の参照整合性規則を施行するため(外部キー制約)

  • 各従業員の会員番号を一意にするため(一意キー制約)

外部キーのUNIQUE制約およびNOT NULL制約

外部キーでUNIQUE制約およびNOT NULL制約が定義されている場合は、子表の1つの行のみが指定された親キーの値を参照できます。また、外部キーにNULL値が許可されないため、子表の各行は親キーの値を明示的に参照する必要があります。

このモデルは、外部キーに未定の値(NULL)を許可しない親キーと外部キーとの間に1対1関連を確立します。前述の例を拡張して、各従業員が一意の会員番号を持つように保証するとともに、従業員表のMEMBERNO列にNOT NULL制約を追加することで、従業員表のMEMBERNO列に未定義の値(NULL)が許可されないようにできます。

12.8.3 複数のFOREIGN KEY制約に関する規則

Oracle Databaseでは、1つの列を複数の外部キー制約で参照できます。依存キーの数に制限はありません。ある列が2つの異なる複合外部キーの一部になっている場合に、この状況が発生する可能性があります。

12.8.4 制約チェックの遅延

Oracle Databaseが制約を確認する場合に、制約が満たされないときは、エラーが表示されます。現行のトランザクションが終了するまで制約のチェックを遅延するには、SET CONSTRAINTS文を使用します。

ノート:

SET CONSTRAINTS文は、トリガー内では使用できません。

制約のチェックを遅延するには次のようにします。

  • データを適切に選択します。

    データに次のいずれかの特性がある場合、一意キーおよび外部キーの制約チェックを遅延する必要があります。

    • 表がスナップショットの場合

    • 別のアプリケーションで処理される大量のデータを含む表であり、同じ順序でデータが戻されるかどうかわからない場合。

  • 外部キーに対するカスケード操作を更新します。

  • 制約が遅延可能かどうかを確認します。

    適切な表を指定した後、表の外部キー、一意キーおよび主キーがDEFERRABLEとして作成されているかどうかを確認します。

  • データを処理するアプリケーション内で、任意のデータを処理する前に、次のようにすべての制約に遅延を設定します。

    SET CONSTRAINTS ALL DEFERRED;

  • (オプション)トランザクションをコミットする直前に制約違反をチェックします。

    COMMIT文の直前に、SET CONSTRAINTS ALL IMMEDIATE文を実行します。制約に問題があった場合、この文は失敗し、エラーの原因になった制約が識別されます。制約が違反しているときにコミットした場合、トランザクションはロールバックされ、エラー・メッセージが表示されます。

例12-4では、表empの主キーと 外部キーがDEFERRABLEとして作成され、遅延されます。

例12-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言語リファレンス』を参照してください。

12.9 制約に対応付けられた索引の領域と時間のオーバーヘッドの最小化

一意キーまたは主キーを作成すると、Oracle Databaseは、既存の索引が制約の一意性を強制するかどうかを確認します。既存の索引がない場合、データベースは索引を作成します。

Oracle Databaseが一意索引を使用して制約を施行し、それに対応付けられた制約が削除または使用禁止にされる場合、索引は削除されます。索引に関連付けられた統計を保持する場合(再作成には時間がかかる)は、DROP CONSTRAINT文にKEEP INDEX句を指定します。

使用可能な外部キーが主キーまたは一意キーを参照している間は、主キー制約または一意キー制約、または索引を使用禁止にしたり削除することはできません。

ノート:

遅延可能制約を使用した一意キーおよび主キーには、非一意索引を使用してください。

一意キー制約および主キー制約の作成時に既存の索引を使用するには、CONSTRAINT句にUSING INDEXを含めます。

関連項目:

整合性制約の詳細と例は、『Oracle Database SQL言語リファレンス』を参照してください。

12.10 外部キーを索引付けするためのガイドライン

一致する一意キーまたは主キーが決して更新または削除されない場合にかぎり、外部キーに索引を付けます。

関連項目:

外部キーの索引付けの詳細は、『Oracle Database概要』を参照してください。

12.11 分散データベース内の参照整合性

参照制約の宣言では、リモート表の主キーまたは一意キーを参照する外部キーを指定できません。

ただし、トリガーを使用すると、複数のノードにまたがる親子の表の関連をメンテナンスできます。

関連項目:

参照整合性を施行するトリガーの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

ノート:

トリガーを使用して分散データベースの複数のノードにまたがる参照整合性を定義する場合、ネットワーク障害が親表および子表へのアクセスを制限する可能性があることに注意してください。

たとえば、子表がSALESデータベースに存在し、親表がHQデータベースに存在すると想定します。

2つのデータベース間のネットワーク接続に障害が発生すると、参照整合性トリガーがHQデータベース内の親表へアクセスする必要があるため、子表に対するデータ操作言語(DML)文が処理(行を挿入したり、外部キーの値を更新するような処理)を進めることができない場合があります。

12.12 CHECK制約を使用する場合

比較などの論理式をベースとした整合性規則を施行する必要がある場合、CHECK制約を使用します。その他のタイプの制約で必要なチェックができる場合には、CHECK制約は使用しないでください。

関連項目:

CHECKおよびNOT NULL制約の選択

CHECK制約の例を次に示します。

  • 給与の値が10000を超えないように、従業員の給与にCHECK制約を定義します。

  • 「BOSTON」、「NEW YORK」および「DALLAS」のみが許可されるように、部門の所在地にCHECK制約を定義します。

  • コミッションの額が給与より多くならないように、給与およびコミッションの列にCHECK制約を定義します。

12.12.1 CHECK制約の制限

CHECK制約では、条件は表のすべての行に対して真または不明である必要があります。条件が偽であると評価された場合、その文はロールバックされます。CHECK制約の条件には、次のような制限があります。

  • 条件は、挿入または更新が行われている行の値を使用して評価できるブール式である必要があります。

  • 条件に副問合せまたは順序を含めることはできません。

  • 条件にSQLファンクションSYSDATE、UID、USERまたはUSERENVを含めることはできません。

  • 条件に疑似列LEVELまたはROWNUMを含めることはできません。

  • 条件にPRIOR演算子を含めることはできません。

  • 条件にユーザー定義ファンクションを含めることはできません。

関連項目:

12.12.2 CHECK制約の設計

CHECK制約は、条件が偽であると評価される場合にのみCHECK制約に違反します。真および不明(NULLと比較して)はチェック条件には違反しません。したがって、定義するCHECK制約が、規則を施行するために十分明確であることを確認してください。

たとえば、次のCHECK制約について考えます。 

CHECK (Sal > 0 OR Comm >= 0)

この規則は、「従業員の給与が0(ゼロ)より大きい場合、または従業員のコミッションが0(ゼロ)以上の場合は、従業員表の行を許可しない」と解釈されます。ただし、給与にNULLの値を持つ行は、チェック条件全体が不明であると評価されるため、そのコミッションの値の評価にかかわらず、CHECK制約に違反しません。このような場合には、SAL列とCOMM列の両方にNOT NULL制約を設定することによって、このような違反を回避できます。

ノート:

どのような場合にNULL条件の結果が不明な値となるかについては、『Oracle Database SQL言語リファレンス』の論理条件の真理値表を参照してください。

12.12.3 複数のCHECK制約に関する規則

1つの列に、その定義で列を参照する複数のCHECK制約を指定できます。定義できるCHECK制約の数に制限はありません。

制約が評価される順序は定義されません。そのため、順序に依存したり、互いに競合するような複数の制約を定義しないでください。

12.12.4 CHECKおよびNOT NULL制約の選択

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))

12.13 制約の定義の例

例12-5および例12-6に、データベース設計のプロトタイプ・フェーズでの簡単な制約の作成方法を示します。これらの例では各制約に名前が付いています。制約に名前を付けると、データ定義言語(DDL)が複数回実行された場合に、システムが生成した異なる名前で、データベースが同じ制約の複数のコピーを作成することを回避できます。

例12-5では、CREATE TABLE文を使用して表と制約を同時に作成します。

例12-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);

例12-6では、ALTER TABLE文を使用して既存の表に制約を作成します。

制約に違反する行が表に存在している場合、VALIDATED状態の制約は作成できません。

例12-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管理者ガイド』を参照してください。

12.13.1 制約の定義に必要な権限

CREATE TABLEまたはCREATE ANY TABLEシステム権限がある場合、作成する表の制約を定義できます。

ALTER ANY TABLEシステム権限ある場合は、既存のすべての表に制約を定義できます。

特定の表のALTERオブジェクト権限がある場合は、その表に制約を定義できます。

一意キーおよび主キー制約では、表の所有者に、UNLIMITED TABLESPACEシステム権限、または対応する索引を含む表領域の割当て制限が必要です。

外部キー制約を定義できるのは、親表またはビューが自分のスキーマ内にある場合、または親表またはビューの参照キー列に対するREFERENCES権限を持っている場合です。

関連項目:

FOREIGN KEY制約の作成に必要な権限

12.13.2 制約のネーミング

NOT NULL、一意キー、主キー、外部キーおよびCHECKの各制約に対して、制約句のCONSTRAINTオプションを使用して名前を割り当ててください。この名前は、そのユーザーが所有している他の制約名に対して一意である必要があります。制約名を指定しない場合、Oracle Databaseが名前を自動的に生成して割り当てます。

独自の名前を指定すると、制約違反のエラー・メッセージがよりわかりやすくなります。また、SQL文が複数回実行された場合に、異なる名前で重複した制約が作成されることを回避できます。

制約句のCONSTRAINTオプションの例として、前述のCREATE TABLE文およびALTER TABLE文の例を参照してください。データ・ディクショナリでは、各制約名が制約に関する他の情報に含まれます。

関連項目:

静的データ・ディクショナリ・ビューの例は、「制約に関する情報の表示」を参照してください

12.14 制約の使用可能および使用禁止

この項では、制約をユーザー自身で使用可能および使用禁止にするしくみ、および手順について説明します。

使用可能にされた制約。制約が使用可能な場合、対応する規則が対応付けられた列のデータ値に施行されます。制約の定義は、データ・ディクショナリ内に格納されます。

使用禁止にされた制約。制約が使用禁止の場合、それに対応する規則は施行されません。制約の定義は、データ・ディクショナリ内に格納されたままです。

整合性制約は、データベース内のデータに関するアサーションを表します。このアサーションは、制約を使用可能にすると必ず真になります。制約を使用禁止にすると、整合性制約に違反するデータがデータベース内に存在する可能性があるため、アサーションは真でない場合もあります。

トピック:

12.14.1 制約を使用禁止にする理由

日常の操作では、制約を使用可能な状態にしておきます。特定の状況においては、パフォーマンス上の理由から、表の制約を一時的に使用禁止にする必要がある場合があります。たとえば:

  • SQL*Loaderを使用して、表に大量のデータをロードする場合

  • 表に対して大規模な変更を行うバッチ作業を実施する場合(たとえば、既存の番号に1000を加えてすべての従業員番号を変更する場合)

  • 表を1つずつインポートまたはエクスポートする場合

制約を一時的に使用禁止にすると、これらの操作が高速になります。

12.14.2 制約を使用可能にする(デフォルト)

整合性制約を定義すると(CREATE TABLEまたはALTER TABLEを使用)、Oracle Databaseではデフォルトで制約が使用可能になります。コードをわかりやすくするために、例12-7のように、制約の定義にENABLE句を含めて制約を明示的に使用可能にできます。

例12-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文は、表の既存の行がその整合性制約に違反するとエラーになります。文はロールバックされ、制約定義は格納されず使用可能にもなりません。

関連項目:

制約に違反する行の詳細は、「制約の例外の修正」を参照してください。

12.14.3 制約を使用禁止にする

整合性制約を定義(CREATE TABLEまたはALTER TABLEを使用)して使用禁止にするには、例12-8のように定義にDISABLE句を指定します。

例12-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文は、その規則が施行されていないため、エラーとなることはありません。

12.14.4 既存の制約を使用可能にする

データをクリーン・アップし、空の列に値を入力した後で、データの挿入中に使用禁止にしていた制約を使用可能にできます。

既存の制約を使用可能にするには、例12-9のようにALTER TABLE文をENABLE句とともに使用します。

例12-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文は、表の行がその整合性制約に違反するとエラーになります。この場合、文はロールバックされ、制約は使用可能になりません。

関連項目:

制約に違反する行の詳細は、「制約の例外の修正」を参照してください。

12.14.5 既存の制約を使用禁止にする

データが含まれている表に対して、大規模な挿入または更新を実行する必要がある場合は、整合性制約を一時的に使用禁止にして、バルク操作のパフォーマンスを向上させることができます。

既存の制約を使用禁止にするには、例12-10のようにALTER TABLE文をDISABLE句とともに使用します。

例12-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;

12.14.6 キー制約の使用可能および使用禁止に関するガイドライン

一意キー、主キーおよび外部キーの各制約を使用可能または使用禁止にする場合は、いくつかの重要な問題および前提条件を認識してください。一意キー制約および主キー制約は、通常、データベース管理者が管理します。

関連項目:

『Oracle Database管理者ガイド』および「FOREIGN KEY制約の管理」

12.14.7 制約の例外の修正

表の行が整合性制約に違反する場合、この行は制約違反になり、制約に対する例外とされます。例外が存在する場合、制約を使用可能にはできません。制約に違反する行は、制約を使用可能にする前に更新または削除する必要があります。

制約を使用可能にするときに、特定の整合性制約に対する例外を指定できます。

関連項目:

この手順の詳細は、「制約の例外の修正」を参照してください

制約を作成または使用可能にするときに、整合性制約の例外があるために文が正常に実行されなかった場合、文はロールバックされます。この場合、すべての例外が更新または削除されるまで制約を使用可能にできません。整合性制約に違反している行を判断するには、CREATE TABLE文またはALTER TABLE文のENABLE句にEXCEPTIONSオプションを指定します。

関連項目:

制約の例外に応答する場合は、『Oracle Database管理者ガイド』を参照してください。

12.15 制約の変更

Oracle8i以降では、例12-11のようにMODIFY CONSTRAINT句を使用して既存の制約状態を変更することができます。

関連項目:

変更するパラメータの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

例12-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;

12.16 制約名の変更

変更可能な制約のプロパティの1つは名前です。制約の名前を変更する状況には次のようなものがあります。

  • 表と制約を複製する場合。

    制約名は、複数のスキーマに対しても一意であることが必要です。このため、複製された表の制約を、元の表の制約と同じ名前にすることができません。

  • 制約をデフォルトのシステム生成名で作成したが、使用可能または使用禁止の設定が容易になるように、覚えやすい制約名が必要な場合。

例12-12に、制約のシステム生成名を検索して変更する方法を示します。

例12-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.

12.17 制約の削除

制約を削除するには、ALTER TABLE文のDROP句を使用します。制約を削除する状況には次のようなものがあります。

  • 制約で施行されるルールが適切でなくなった場合。

  • 制約が不要になった場合。

1つの制約と、その制約が依存している他のすべての整合性制約を削除するには、CASCADEを指定します。

例12-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;

一意キー、主キーおよび外部キーの各制約を削除する場合は、いくつかの重要な問題および前提条件を認識してください。一意キー制約および主キー制約は、通常、データベース管理者が管理します。

関連項目:

12.18 FOREIGN KEY制約の管理

外部キー制約は、異なる表の列同士の関連を規定します。したがって、参照中の主キーまたは一意キーの制約が存在していないか、または使用可能になっていない場合は、制約を使用可能にできません。

12.18.1 外部キー列のデータ型および名前

依存する側の表と参照される側の表の対応する列は、同じデータ型である必要があります。列名が一致する必要はありません。

12.18.2 複合外部キーにおける列の制限

外部キーは、親表の主キーまたは一意キーを参照し、主キー制約および一意キー制約は索引を使用して施行されるため、複合外部キーは32列以内に制限されています。

12.18.3 デフォルトでの外部キーの主キー参照

外部キー制約(単一列または複合列)を定義するときに、REFERENCESオプションに列リストが指定されていないと、Oracle Databaseは、指定した表の主キーが参照されるものとみなします。または、カッコの中に親表で参照する列を明示的に指定できます。この列リストが親表の主キーまたは一意キーを参照するかどうかは、Oracle Databaseによって自動的に確認されます。参照していない場合は、エラー情報が戻されます。

12.18.4 FOREIGN KEY制約の作成に必要な権限

外部キー制約を作成するには、制約の作成者に親表および子表に対するアクセス権限が必要です。

  • 親表 参照整合性制約の作成者は、親表を所有するか、または親表の親キーを構成する列に対するREFERENCESオブジェクト権限が必要です。

  • 子表 参照整合性制約の作成者は、表を作成する権限(CREATE TABLEまたはCREATE ANY TABLEシステム権限)または子表を変更する権限(子表のALTERオブジェクト権限またはALTER ANY TABLEシステム権限)が必要です。

どちらの場合も、必要な権限をロールを介して取得することはできません。権限は、明示的に制約の作成者に付与する必要があります。

これらの制限によって、次のことが可能となります。

  • 子表の所有者は、施行される制約の種類、または制約を作成できる他のユーザーを、明示的に決定できます。

  • 親表の所有者は、外部キーが所有者自身の表の主キーおよび一意キーに依存可能であるかどうかを明示的に決定できます。

12.18.5 外部キーによる参照整合性の施行方法の選択

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);

12.19 制約に関する情報の表示

制約の名前、影響がある列および制約の管理に有効な他の情報を検索するには、例12-14のように静的データ・ディクショナリ・ビュー*_CONSTRAINTSおよび*_CONS_COLUMNSに問い合せます。

関連項目:

*_CONSTRAINTSおよび*_CONS_COLUMNSの詳細は、『Oracle Databaseリファレンス』を参照してください。

例12-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列に別々の文字で表示されます。次の表に、各制約タイプに対応する文字を示します。

    制約タイプ 文字

    PRIMARY KEY

    P

    UNIQUE KEY

    U

    FOREIGN KEY

    R

    CHECK、NOT NULL

    C

    ノート:

    その他の制約タイプで、CONSTRAINT_TYPE列に「V」の文字で表示されるものがあります。この制約タイプは、ビューに対するWITH CHECK OPTIONを使用して作成された制約に対応しています。

次の制約は、SEARCH_CONDITION列に明示的にリストされます。

  • NOT NULL制約

  • ユーザー定義CHECK制約の条件