12 データベース・アプリケーションにおけるデータ整合性のメンテナンス
データベース・アプリケーションでは、データ整合性のメンテナンスは、アプリケーションで操作される表のデータが適切なビジネス・ルールに準拠する必要があることを意味します。ビジネス・ルールは、常に真または偽になる必要がある条件および関連を指定します。たとえば、従業員の給与が$100,000を超えないようにするビジネス・ルールや、EMPLOYEES
表のすべての従業員がDEPARTMENTS
表の部門に所属する必要があるビジネス・ルールなどがあります。各企業では、給与、従業員番号、在庫追跡などに関して独自のポリシーを定義しているため、企業ごとにビジネス・ルールが異なります。
データ整合性を確認する方法は複数あり、可能なかぎり、整合性制約(または制約)による方法を使用する必要があります。
この章は、次の情報を補足します。
ノート:
この章は、表に対する制約のみに適用されます。ビューに対する制約では、データ整合性をメンテナンスしたり、関連する索引を持つことはできません。これらは、ビューを伴う問合せをリライトできるため、マテリアライズド・ビューおよびその他のデータ・ウェアハウス機能を使用した場合のパフォーマンスが向上します。
関連項目:
-
データ整合性および制約の詳細は、『Oracle Database概要』を参照してください。
-
制約管理の詳細は、『Oracle Database管理者ガイド』を参照してください。
-
制約に関する構文およびセマンティクスの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
-
ビューに関する制約の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
-
データ・ウェアハウスでの制約の使用の詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。
-
データ整合性においてデータ型が担う役割の詳細は、「正しいデータ型によるデータ整合性の向上」を参照してください。
トピック:
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
に格納されています。この情報は、静的データ・ディクショナリ・ビューまたは動的なパフォーマンス・ビューには表示されません。有効な一意キー制約または主キー制約が索引を使用している場合は、索引のみを削除することはできません。索引を削除するには、制約自身を削除するか、その制約を無効にしてから索引を削除する必要があります。
関連項目:
-
制約の無効化および削除の詳細は、『Oracle Database管理者ガイド』を参照してください。
-
明示的な索引作成の詳細は、『Oracle Database管理者ガイド』を参照してください。
-
明示的な索引作成の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
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
には、NOTNULL
制約は含まれません
。 -
表の索引スキャンを実行したり、すべての行の索引付けが必要な操作を実行できるようにする場合。
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
と一意
制約を組み合せると、一意
キーに必ず値が入力され、新しい行のデータと既存の行のデータが競合する可能性がなくなります。
関連項目:
-
NOT
NULL
制約の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 -
デフォルト列値の使用の詳細は、「デフォルトの列値を使用する場合」を参照してください。
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
);
関連項目:
-
INSERT
文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 -
CREATE
TABLE
文の詳細は、Oracle Database SQL言語リファレンスを参照 -
ALTER
TABLE
文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 -
NOT
NULL
制約の詳細は、「NOT NULL制約を使用する場合」を参照してください。
12.6 表の主キーの選択(PRIMARY KEY制約)
表の主キーは各行を一意に識別し、行の重複も回避できます(通常はこの目的のみ)。したがって、主キーの値をNULL
にすることはできません。
表に含めることができる主キーは最大で1つですが、複数の列を含めることができます(複合キーが可能)。主キーを指定するには、主
キー
制約を使用します。
実用的なものとしては、値が順次生成される単一列を主キーとして選択します。
主キーとして選択するもので、次に最適なのは、値が次のすべてに該当する単一列です。
-
一意
-
変更なし
-
非
NULL
-
短い数値(入力が簡単)
値が長く順次生成できない複合主キーの使用については、最小限にします。
関連項目:
-
順序の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
12.7 UNIQUE制約を使用する場合
非
制約を使用します。たとえば:
NULL
値の重複が許可されていない1つの列または複数の列の組合せ(主キーを除く)では、一意
一意キー | 主キー |
---|---|
従業員の社会保障番号 |
従業員番号 |
トラックのナンバープレートの番号 |
トラック番号 |
顧客の電話番号(国コード列、市外局番列、市内局番列) |
顧客番号 |
部門名列および拠点列 |
部門番号 |
図12-1は、UNIQUE
制約を使用した表、制約に違反している行、および制約を満たしている行を示しています。
12.8 FOREIGN KEY制約を使用した参照整合性の施行
2つの表に1つ以上の共通の列が含まれる場合、外部
キー
制約を使用して、参照整合性(両方の表で共通の列に常に同じ値が含まれるようにする)を施行できます。
ノート:
外部
キー
制約は参照整合性制約とも呼ばれ、静的データ・ディクショナリ・ビュー*_CONSTRAINTS
では、そのCONSTRAINT_TYPE
は、R
になります。
一方の表を参照表または親表として指定し、もう一方の表を依存または子表として指定します。親表では、共通する列に主
キー
制約または一意
制約を定義します。子表では、共通する列に外部
キー
制約を定義します。これで、共通する列は外部キーで構成されます。外部キーに追加で制約を定義すると、親子関係に影響します。
図12-2に、部門番号に定義された外部キーを示します。この外部キーは、この列の値がそれぞれ部門表の主キーの値と一致することを保証します。制約によって、間違った部門番号が従業員表に入力される可能性を回避できます。
図12-2は、1つの列を共有する親表と子表、外部
キー
制約に違反している行、およびその制約を満たしている行を示しています。
トピック:
関連項目:
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
部分が、参照表の主キーまたは一意キー列の対応する部分にあることを要求する)を施行することはできません。多くの場合、トリガーを使用してこのような状況に対応できます。
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
制約の例を次に示します。
-
給与の値が10000を超えないように、従業員の給与に
CHECK
制約を定義します。 -
「
BOSTON
」、「NEW
YORK
」および「DALLAS
」のみが許可されるように、部門の所在地にCHECK
制約を定義します。 -
コミッションの額が給与より多くならないように、給与およびコミッションの列に
CHECK
制約を定義します。
12.12.1 CHECK制約の制限
CHECK
制約では、条件は表のすべての行に対して真または不明である必要があります。条件が偽であると評価された場合、その文はロールバックされます。CHECK
制約の条件には、次のような制限があります。
-
条件は、挿入または更新が行われている行の値を使用して評価できるブール式である必要があります。
-
条件に副問合せまたは順序を含めることはできません。
-
条件にSQLファンクション
SYSDATE
、UID
、USER
またはUSERENV
を含めることはできません。 -
条件に疑似列
LEVEL
またはROWNUM
を含めることはできません。 -
条件に
PRIOR
演算子を含めることはできません。 -
条件にユーザー定義ファンクションを含めることはできません。
関連項目:
-
LEVEL
擬似列の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 -
ROWNUM
擬似列の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 -
PRIOR
演算子(階層問合せで使用)の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
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
権限を持っている場合です。
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 キー制約の使用可能および使用禁止に関するガイドライン
一意
キー、主
キー
および外部
キー
の各制約を使用可能または使用禁止にする場合は、いくつかの重要な問題および前提条件を認識してください。一意
キー制約および主
キー
制約は、通常、データベース管理者が管理します。
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;
一意
キー、主
キー
および外部
キー
の各制約を削除する場合は、いくつかの重要な問題および前提条件を認識してください。一意
キー制約および主
キー
制約は、通常、データベース管理者が管理します。
関連項目:
-
ALTER
TABLE
文のDROP
句の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 -
制約の削除の詳細は、『Oracle Database管理者ガイド』を参照してください。
-
DROP
TABLE
文のCASCADE
CONSTRAINTS
句の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。この方法では、削除される表の主キーと一意キーを参照するすべての参照整合性制約が削除されます。
12.18 FOREIGN KEY制約の管理
外部
キー
制約は、異なる表の列同士の関連を規定します。したがって、参照中の主キーまたは一意キーの制約が存在していないか、または使用可能になっていない場合は、制約を使用可能にできません。
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
制約の条件