10gリリース2（10.2）
B19248-02
 目次 |
 索引 |
| Oracle Database アプリケーション開発者ガイド-基礎編 10gリリース2（10.2） B19248-02 |
|
この章では、データベースに対応付けたビジネス・ルール（業務規則）を施行する方法、および整合性制約を使用して表に無効な情報が入力されないようにする方法について説明します。内容は次のとおりです。
整合性制約を使用して表のデータにビジネス・ルールを施行できます。ビジネス・ルールは、常に真または偽になる必要がある条件および関連を指定します。給与、従業員数、在庫調査などに関する方針は会社ごとに異なるため、それぞれのデータベース表に対して異なる規則を指定できます。
整合性制約が表に適用された場合、表のすべてのデータは対応する規則に準拠する必要があります。表のデータを変更するSQL文を発行すると、Oracle Databaseは新しいデータが整合性制約を満たしていることを保証します。プログラムで確認する必要はありません。
整合性制約を定義して規則を施行すると、アプリケーションにロジックを追加して同じ規則を施行するより信頼性が高くなります。Oracle Databaseは、アプリケーションよりも高速に表のデータが整合性制約に従っているかどうかを確認できます。
各従業員が有効な部門に所属しているかどうかを確認するには、まず部門表のすべての値が一意であるという規則を作成します。
ALTER TABLE Dept_tab ADD PRIMARY KEY (Deptno);
次に、従業員表に示されるすべての部門が、部門表内のいずれかの値と一致するという規則を作成します。
ALTER TABLE Emp_tab ADD FOREIGN KEY (Deptno) REFERENCES Dept_tab(Deptno);
これ以降、表に新しい従業員レコードを追加すると、その部門番号が部門表に存在するかどうかがOracle Databaseによって確認されます。
整合性制約を使用しないでこの規則を施行するには、部門表を問い合せるトリガーを使用して、新しい従業員の部門が有効かどうかをテストします。ただし、この方法は整合性制約より信頼性が低くなります。Oracle DatabaseのSELECT文では読取り一貫性が使用されるため、この問合せは、他のトランザクションからのコミットされていない変更を見落とす場合があります。
挿入または更新を行う前に不正なデータをフィルタ除去できる場合は、アプリケーション・ロジックおよび整合性制約を介してビジネス・ルールを施行できます。これによって、ユーザーにすぐにフィードバックでき、データベースの負荷を削減できます。この方法は、表内のデータを確認しなくても、データの値が不正または範囲外であると判断できる場合に適しています。
使用可能なすべての一意キーおよび主キーには、対応する索引が必要です。これらの索引は、データベースに自動的に作成させるのではなく、手動で作成する必要があります。次のことに注意してください。
CDEF$.ENABLEDに格納されています。カタログ・ビューには表示されません。
ほとんどの場合、外部キーには索引を付ける必要があります。データベース側ではこの作業が行われません。
デフォルトでは、すべての列がNULLを含むことができます。NOT NULL制約は、常に値が必要とされる表の列のみに定義します。
たとえば、一時的に新しい従業員のマネージャまたは入社日に値が入っていなくても、特に問題はありません。また、従業員の中には、コミッション（歩合）を受けていない人もいます。これらの列は、NOT NULL整合性制約の対象としては適切ではありません。ただし、従業員の名前は最初から必要であるため、NOT NULL整合性制約を使用してこの規則を施行できます。
NOT NULL制約を他の整合性制約と組み合せて、表の特定の列に存在できる値をさらに制限することがよくあります。一意キーに必ず値を入力するには、NOT NULLと一意キー整合性制約を組み合せます。このようにデータ整合性規則を組み合せることで、新しい行のデータと既存の行のデータが競合する可能性はなくなります。
Oracle Database索引は、すべてがNULLのキーは格納しません。したがって、表の索引のみをスキャンする場合、またはすべての行に索引付けが必要な操作を実行する場合は、1つ以上の索引列にNOT NULL制約を付けます。
NOT NULL制約は、次のとおり指定します。
ALTER TABLE emp MODIFY ename NOT NULL;
図6-1に、NOT NULL整合性制約を使用した表の例を示します。
デフォルト値は、代表的な値を含む列に割り当てます。たとえば、DEPT_TAB表において、ほとんどの部門がある1つの所在地に位置する場合、この値（NEW YORKなど）をLOC列のデフォルト値に設定できます。
エントリを持たない列に0（ゼロ）などのデフォルト値が適用される場合、エラーの回避に有効です。たとえば、デフォルト値を0（ゼロ）に設定すると、次のテストは、
IF sal IS NOT NULL AND sal < 50000
次のように、より簡単な書式に変更できます。
IF sal < 50000
ビジネス・ルールに応じて、デフォルト値を0（ゼロ）または偽に設定できます。また、デフォルト値をNULLのままにして、不明な値を示すこともできます。
また、表の一部の列を参照可能にするためのビューを作成する場合も、デフォルトは有効です。たとえば、ビューを介してユーザーが行を挿入できるようにしていることがあります。実表には、ビューの定義には含まれていない列で、各行を挿入するユーザーを記録するINSERTER列がある場合もあります。ユーザー名を自動的に記録するには、USER関数をコールするデフォルト値を定義します。
CREATE TABLE audit_trail
(
value1 NUMBER,
value2 VARCHAR2(32),
inserter VARCHAR2(30) DEFAULT USER
);
デフォルト値は、任意のリテラルまたはほとんどの式を使用して定義できます。これには、次のコールも含まれます。
デフォルト値には、順序、PL/SQLファンクション、列、LEVEL、ROWNUMまたはPRIORを参照する式を含めることはできません。デフォルトのリテラルまたは式のデータ型は、その列のデータ型と一致しているか、または変換できる必要があります。
デフォルト値が、SQL関数の結果の場合もあります。たとえば、SYS_CONTEXTをコールすると、ユーザー名などの条件に応じて異なるデフォルト値が設定されます。デフォルト値として使用するには、SQL関数のパラメータがすべてリテラルで、他の列の参照および他の関数のコールができない必要があります。
デフォルト値が列に対して明示的に定義されない場合、その列のデフォルトは暗黙的にNULLに設定されます。
INSERT文内で、リテラル値のかわりにDEFAULTキーワードを使用できます。これによって、対応するデフォルト値が挿入されます。
表ごとに、1つの主キーを含めることができます。主キーを使用すると、表の各行を一意に識別でき、また、行の重複も回避できます。主キーを選択するときには、次のガイドラインに従ってください。
主キー制約では、定義によって主キーを構成する列にNULLが含まれている行は許可されません。
一意キーに指定する列は、慎重に選択してください。この制約の目的は、主キーの目的とは異なります。一意キー制約は、値の重複が許されない列に適しています。主キーは表の各行を一意に識別し、通常、一意であること以外は重要ではない値を含みます。図6-2に、一意キー整合性制約を使用した表の例を示します。
適切な一意キーの使用例は次のとおりです。
AREA_CODEとLOCAL_PHONEの2列からなる顧客電話番号（主キーは顧客番号）
この章全体を通して説明している制約は、ビューではなく表に適用されるものです。
ビューにも制約を宣言できますが、その制約はデータの整合性を保持するには有効ではありません。これらの制約は、ビューを伴う問合せをリライトするために使用されます。これによって、マテリアライズド・ビューおよびその他のデータ・ウェアハウス機能を使用した場合のパフォーマンスが向上します。これらの制約は、常にDISABLEキーワードを使用して宣言されます。VALIDATEキーワードは使用できません。この場合、制約は施行されず、対応付けられた索引はありません。
2つの表に1つ以上の共通の列が含まれる場合、Oracle Databaseでは、参照整合性制約を使用して2つの表に関連を規定できます。親表（完全な列値の集合を持つ表）の列に、主キー制約または一意キー制約を定義します。子表の列（他の表の値を参照する値を含む表）には、外部キー制約を定義します。
図6-3に、部門番号に定義された外部キーを示します。この外部キーは、この列の値がそれぞれ部門表の主キーの値と一致することを保証します。制約によって、間違った部門番号が従業員表に入力される可能性を回避できます。
外部キーは、複数の列で構成することもできます。このような複合外部キーは、正確に同じ構造（列の数とデータ型が同一）を持つ複合主キーまたは複合一意キーを参照する必要があります。複合主キーまたは複合一意キーには32列までという制限があるため、複合外部キーも最大32列に制限されます。
外部キーには、一致する主キーまたは一意キーがない場合でも、すべて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以外の列が、参照先の主キーまたは一意キー列の同等の位置にあること）を施行することはできません。この場合、第9章「トリガーのコーディング」で説明するように、トリガーを使用して処理できることがよくあります。
親表と子表との関連のいくつかは、子表の外部キーで定義されている他のタイプの整合性制約によって判断できます。
外部キーについて他の制約が定義されていない場合は、子表の行は何行でも同一の親キー値を参照できます。このモデルでは、外部キーにNULLが許可されます。
このモデルは、外部キーに未定の値（NULL）を許可する親キーと外部キーとの間に1対多関連を確立します。従業員表と部門表の間のこのような関連の例を、図6-1「NOT NULL整合性制約を使用した表」に示します。各部門（親キー）には多数の従業員（外部キー）が所属しますが、一部の従業員は、部門に所属していない場合があります（外部キーでNULL）。
外部キーでNULLが許可されていない場合は、子表の各行は親キーの値を明示的に参照する必要があります。
子表の行は何行でも同一の親キー値を参照できるため、このモデルは、親キーと外部キーとの1対1関連を確立します。ただし、子表の各行は、必ず親キー値に対する参照を持っている必要があり、外部キーに値の欠如（NULL）があってはいけません。前述の項の例を使用して、この関連を説明できます。ただし、このモデルでは、従業員は必ず特定の部門への参照を持つ必要があります。
外部キーに一意キー制約が定義されている場合は、子表の中の1行のみが、親キー値を参照できます。このモデルでは、外部キーにNULLが許可されます。
このモデルは、外部キーに未定の値（NULL）を許可する親キーと外部キーとの間に1対1関連を確立します。たとえば、従業員表に、企業の保険計画の従業員の会員番号を参照するMEMBERNOという名前の列があると想定します。また、INSURANCEという表には、MEMBERNOという主キーがあり、その他の列は保険証書に関連した各従業員の情報を保持しているとします。次の理由によって、従業員表のMEMBERNOは、外部キーかつ一意キーである必要があります。
一意キー制約およびNOT NULL制約の両方が外部キーに定義されている場合は、子表の1行のみが指定された親キー値を参照できます。外部キーではNULL値を使用できないため、子表の各行は、親キーの値を明示的に参照する必要があります。
このモデルは、外部キーに未定の値（NULL）を許可しない親キーと外部キーとの間に1対1関連を確立します。前述の例を拡張して、各従業員が一意の会員番号を持つように保証するとともに、従業員表のMEMBERNO列にNOT NULL制約を追加することで、従業員表のMEMBERNO列に未定義の値（NULL）が許可されないようにできます。
Oracle Databaseでは、1つの列を複数の外部キー制約で参照できます。依存キーの数に制限はありません。ある列が2つの異なる複合外部キーの一部になっている場合に、この状況が発生する可能性があります。
Oracle Databaseが制約を確認する場合に、制約が満たされないときは、エラーが表示されます。SET CONSTRAINTS文を使用して、トランザクションが終了するまで制約の妥当性チェックを遅延できます。
SET CONSTRAINTSの設定は、トランザクションが終了するか、または別のSET CONSTRAINTS文でモードがリセットされるまで継続されます。
制約チェックを遅延するときには、次のガイドラインを検討してください。
データに次のいずれかの特性がある場合、一意キーおよび外部キーの制約チェックを遅延する必要があります。
適切な表を識別し、選択した後、表の外部キー、一意キーおよび主キーが遅延可能に作成されているかどうかを確認します。確認するには、次のような文を発行します。
CREATE TABLE dept (
deptno NUMBER PRIMARY KEY,
dname VARCHAR2 (30)
);
CREATE TABLE emp (
empno NUMBER,
ename VARCHAR2 (30),
deptno NUMBER REFERENCES (dept),
CONSTRAINT epk PRIMARY KEY (empno) DEFERRABLE,
CONSTRAINT efk FOREIGN KEY (deptno)
REFERENCES (dept.deptno) DEFERRABLE);
INSERT INTO dept VALUES (10, 'Accounting');
INSERT INTO dept VALUES (20, 'SALES');
INSERT INTO emp VALUES (1, 'Corleone', 10);
INSERT INTO emp VALUES (2, 'Costanza', 20);
COMMIT;
SET CONSTRAINT efk DEFERRED;
UPDATE dept SET deptno = deptno + 10
WHERE deptno = 20;
SELECT * from emp ORDER BY deptno;
EMPNO ENAME DEPTNO
----- -------------- -------
1 Corleone 10
2 Costanza 20
UPDATE emp SET deptno = deptno + 10
WHERE deptno = 20;
SELECT * FROM emp ORDER BY deptno;
EMPNO ENAME DEPTNO
----- -------------- -------
1 Corleone 10
2 Costanza 30
COMMIT;
データを処理するアプリケーション内で、任意のデータを処理する前に、すべての制約に遅延を設定する必要があります。遅延可能なすべての制約に遅延を設定するには、次のDML文を使用します。
SET CONSTRAINTS ALL DEFERRED;
COMMITを発行する直前にSET CONSTRAINTS ALL IMMEDIATE文を発行すると、コミットする前に制約違反を確認できます。制約に問題があった場合、この文は失敗し、エラーの原因になった制約が識別されます。制約が違反しているときにコミットした場合、トランザクションはロールバックされ、エラー・メッセージが表示されます。
一意キーまたは主キーを作成すると、Oracle Databaseは、制約の一意性を強制するために既存の索引が使用できるかどうかを確認します。既存の索引がない場合、データベースは索引を作成します。
Oracle Databaseが一意索引を使用して制約を施行し、それに対応付けられた制約が削除または使用禁止にされる場合、索引は削除されます。索引に関連付けられた統計を保持する場合（索引の再作成に時間がかかる場合など）は、制約のDROPコマンドにKEEP INDEX句を指定できます。
使用可能な外部キーが主キーまたは一意キーを参照している間は、主キー制約または一意キー制約、または索引を使用禁止にしたり削除することはできません。
一意キー制約および主キー制約の作成時に既存の索引を再利用するには、制約句にUSING INDEXを含めます。次に例を示します。
CREATE TABLE b ( b1 INTEGER, b2 INTEGER, CONSTRAINT unique1 (b1, b2) USING INDEX (CREATE UNIQUE INDEX b_index on b(b1, b2), CONSTRAINT unique2 (b1, b2) USING INDEX b_index );
ほとんどの場合、外部キーには索引を付ける必要があります。一致する一意キーまたは主キーが決して更新または削除されない場合にのみ、索引を付ける必要はありません。
参照整合性制約の宣言では、リモート表の主キーまたは一意キーを参照する外部キーを指定できません。
ただし、トリガーを使用すると、複数のノードにまたがる親子の表の関連をメンテナンスできます。
比較などの論理式をベースとした整合性規則を施行する必要がある場合、CHECK制約を使用します。その他のタイプの整合性制約で必要なチェックができる場合には、CHECK制約は使用しないでください。
CHECK制約の例を次に示します。
CHECK制約を定義します。
BOSTON」、「NEW YORK」および「DALLAS」のみが許可されるように、部門の所在地にCHECK制約を定義します。
CHECK制約を定義します。
CHECK整合性制約では、条件は表のすべての行に対して真または不明である必要があります。条件が偽であると評価された場合、その文はロールバックされます。CHECK制約の条件には、次のような制限があります。
SYSDATE、UID、USERまたはUSERENVを含めることはできません。
LEVEL、PRIORまたはROWNUMを含めることはできません。
CHECK制約は、条件が偽であると評価される場合にのみCHECK制約に違反します。真および不明（NULLと比較して）はチェック条件には違反しません。したがって、定義するCHECK制約が、規則を施行するために十分明確であることを確認してください。
たとえば、次のCHECK制約について考えます。
CHECK (Sal > 0 OR Comm >= 0)
この規則は、「従業員の給与が0（ゼロ）より大きい場合、または従業員のコミッションが0（ゼロ）以上の場合は、従業員表の行を許可しない」と解釈されます。ただし、給与にNULLの値を持つ行は、チェック条件全体が不明であると評価されるため、そのコミッションの値の評価にかかわらず、CHECK制約に違反しません。このような場合には、SAL列とCOMM列の両方にNOT NULL整合性制約を設定することによって、このような違反を回避できます。
1つの列に、その定義で列を参照する複数のCHECK制約を指定できます。定義できるCHECK制約の数に制限はありません。
制約が評価される順序は定義されません。そのため、順序に依存したり、互いに競合するような複数の制約を定義しないでください。
ANSI/ISO規格によると、NOT NULL整合性制約はCHECK整合性制約の1つであり、その条件は次のとおりです。
CHECK (Column_name IS NOT NULL)
このため、単一列に対するNOT NULL整合性制約は、実際には、NOT NULL制約またはCHECK制約を使用して2種類の形式で記述できます。使用しやすさという点で、IS NOT NULL条件を指定したCHECK制約でなく、NOT 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))
次に、データベース設計のプロトタイプ・フェーズでの簡単な制約の作成方法を示します。
各制約には、これらの例の名前が付けられます。制約に名前を付けると、DDLが複数回実行された場合に、システムが生成した異なる名前で、データベースが同じ制約の複数のコピーを作成することを回避できます。
次のCREATE TABLE文で、いくつかの整合性制約の定義の具体例を示します。
CREATE TABLE Dept_tab ( Deptno NUMBER(3) CONSTRAINT Dept_pkey PRIMARY KEY, Dname VARCHAR2(15), Loc VARCHAR2(15), CONSTRAINT Dname_ukey UNIQUE (Dname, Loc), CONSTRAINT Loc_check1 CHECK (loc IN ('NEW YORK', 'BOSTON', 'CHICAGO'))); CREATE TABLE Emp_tab ( Empno NUMBER(5) CONSTRAINT Emp_pkey PRIMARY KEY, Ename VARCHAR2(15) NOT NULL, Job VARCHAR2(10), Mgr NUMBER(5) CONSTRAINT Mgr_fkey REFERENCES Emp_tab, Hiredate DATE, Sal NUMBER(7,2), Comm NUMBER(5,2), Deptno NUMBER(3) NOT NULL CONSTRAINT dept_fkey REFERENCES Dept_tab ON DELETE CASCADE);
整合性制約は、ALTER TABLEコマンドの制約句を使用しても定義できます。次に例を示します。
CREATE UNIQUE INDEX I_dept ON Dept_tab(deptno); ALTER TABLE Dept_tab ADD CONSTRAINT Dept_pkey PRIMARY KEY (deptno); ALTER TABLE Emp_tab ADD CONSTRAINT Dept_fkey FOREIGN KEY (Deptno) REFERENCES Dept_tab; ALTER TABLE Emp_tab MODIFY (Ename VARCHAR2(15) NOT NULL);
制約に違反する行が表にすでに存在している場合、VALIDATED状態の制約は作成できません。
制約を作成するには、制約のある表を作成する権限（CREATE TABLEまたはCREATE ANY TABLEシステム権限）または変更する権限（表に対するALTERオブジェクト権限またはALTER ANY TABLEシステム権限）が必要です。さらに、一意キーおよび主キー整合性制約では、表の所有者に、対応する索引を含む表領域の割当て制限またはUNLIMITED TABLESPACEシステム権限のいずれかが必要です。外部キー整合性制約では、その他にもいくつかの権限が必要です。
NOT NULL、一意キー、主キー、外部キーおよびCHECKの各制約に対して、制約句のCONSTRAINTオプションを使用して名前を割り当ててください。この名前は、そのユーザーが所有している他の制約名に対して一意である必要があります。制約名を指定しない場合、Oracle Databaseが名前を自動的に生成して割り当てます。
独自の名前を指定すると、制約違反のエラー・メッセージがよりわかりやすくなります。また、SQL文が複数回実行された場合に、異なる名前で重複した制約が作成されることを回避できます。
制約句のCONSTRAINTオプションの例として、前述のCREATE TABLE文およびALTER TABLE文の例を参照してください。なお、データ・ディクショナリでは、制約名の他に、その制約に関する他の情報も参照できます。
この項では、整合性制約をユーザー自身で使用可能および使用禁止にするしくみ、および手順について説明します。
使用可能にされた制約 制約が使用可能な場合、対応する規則が対応付けられた列のデータ値に施行されます。制約の定義は、データ・ディクショナリ内に格納されます。
使用禁止にされた制約 制約が使用禁止の場合、それに対応する規則は施行されません。制約の定義は、データ・ディクショナリ内に格納されたままです。
整合性制約は、データベース内のデータに関するアサーションを表します。このアサーションは、制約を使用可能にすると必ず真になります。制約を使用禁止にすると、整合性制約に違反するデータがデータベース内に存在する可能性があるため、アサーションは真である場合も真でない場合もあります。
日常の操作では、制約は常に使用可能にしておく必要があります。特定の状況においては、パフォーマンス上の理由から、表の整合性制約を一時的に使用禁止にする必要がある場合があります。次に例を示します。
整合性制約を一時的に使用禁止にすると、これらの操作が高速になります。
表の行が整合性制約に違反する場合、この行は制約違反になり、制約に対する例外とされます。例外が存在する場合、制約を使用可能にはできません。制約に違反する行は、制約を使用可能にする前に更新または削除する必要があります。
制約を使用可能にするときに、特定の整合性制約に対する例外を指定できます。
CREATE TABLE文またはALTER TABLE文で整合性制約を定義するときには、Oracle Databaseはその制約を自動的に使用可能にします。コードをわかりやすくするために、その定義にENABLE句を含めることによって、制約を明示的に使用可能にできます。
初期状態が空で、個々のトランザクションによって1つずつ行が移入される表を作成する場合は、この方法を使用します。この場合は、データが常に一貫しており、各DML操作におけるパフォーマンス上のオーバーヘッドが小さいことを確認する必要があります。
次のCREATE TABLE文およびALTER TABLE文は、どちらも整合性制約を定義し、使用可能にします。
CREATE TABLE Emp_tab ( Empno NUMBER(5) PRIMARY KEY); ALTER TABLE Emp_tab ADD PRIMARY KEY (Empno);
整合性制約を使用可能にするALTER TABLE文は、表の既存の行がその整合性制約に違反するとエラーになります。文はロールバックされ、制約定義は格納されず使用可能にもなりません。
次のCREATE TABLE文およびALTER TABLE文は、どちらも整合性制約を定義し、使用禁止にします。
CREATE TABLE Emp_tab ( Empno NUMBER(5) PRIMARY KEY DISABLE); ALTER TABLE Emp_tab ADD PRIMARY KEY (Empno) DISABLE;
大量のデータがロードされる表を作成する場合は、他のユーザーが表にアクセスする前にこの方法を使用します。特に、データのロード後にデータをクリーン・アップしたり、空の列に順序番号または親子関係を埋める必要がある場合です。
整合性制約を定義して使用禁止にするALTER TABLE文は、その規則が施行されていないため、エラーとなることはありません。
次の目的でALTER TABLEコマンドを使用します。
データをクリーン・アップしたり、空の列を埋めた後で、データのロード中に使用禁止にされていた制約を使用可能にできます。
次の2つの文は、使用禁止にされた整合性制約を使用可能にする例です。
ALTER TABLE Dept_tab ENABLE CONSTRAINT Dname_ukey; ALTER TABLE Dept_tab ENABLE PRIMARY KEY ENABLE UNIQUE (Dname) ENABLE UNIQUE (Loc);
整合性制約を使用可能にするALTER TABLE文は、表の行がその整合性制約に違反するとエラーになります。この場合、文はロールバックされ、制約は使用可能になりません。
すでにデータが含まれている表に対して、大規模なロードまたは更新を実行する必要がある場合は、整合性制約を一時的に使用禁止にして、バルク操作のパフォーマンスを向上させることができます。
次の文は、使用可能にされた整合性制約を使用禁止にする例です。
ALTER TABLE Dept_tab DISABLE CONSTRAINT Dname_ukey; ALTER TABLE Dept_tab DISABLE PRIMARY KEY DISABLE UNIQUE (Dname) DISABLE UNIQUE (Loc);
前述の例では、関連する制約の名前および影響される列を把握している必要があります。この情報を検索するには、制約に対して定義されているデータ・ディクショナリ・ビューUSER_CONSTRAINTSまたはUSER_CONS_COLUMNSの1つを問い合せます。これらのビューの詳細は、「整合性制約の定義のビュー」および『Oracle Databaseリファレンス』を参照してください。
一意キー、主キーおよび外部キーの各整合性制約を使用可能または使用禁止にする場合は、いくつかの重要な問題および前提条件を認識しておく必要があります。一意キー制約および主キー制約は、通常、データベース管理者が管理します。
制約を作成または使用可能にするときに、整合性制約の例外があるために文が正常に実行されなかった場合、文はロールバックされます。この場合、すべての例外が更新または削除されるまで制約を使用可能にできません。整合性制約に違反している行を判断するには、CREATE TABLE文またはALTER TABLE文のENABLE句にEXCEPTIONSオプションを指定します。
Oracle8i以上では、MODIFY CONSTRAINT句を使用して、既存の制約状態を変更できます。
次のコマンドは、CHECK制約が施行されるかどうか、およびその制約チェックが実行されたときの選択肢を示します。
CREATE TABLE X1_tab (a1 NUMBER CONSTRAINT y CHECK (a1>3) DEFERRABLE DISABLE); ALTER TABLE X1_tab MODIFY CONSTRAINTY_cnstrtENABLE; ALTER TABLE X1_tab MODIFY CONSTRAINTY_cnstrtRELY; ALTER TABLE X1_tab MODIFY CONSTRAINTY_cnstrtINITIALLY DEFERRED; ALTER TABLE X1_tab MODIFY CONSTRAINTY_cnstrtENABLE NOVALIDATE;
次のコマンドは、NOT NULL制約が施行されるかどうか、およびその制約チェックが実行されたときの選択肢を示します。
CREATE TABLE X1_tab (A1 NUMBER CONSTRAINT Y_cnstrt NOT NULL DEFERRABLE INITIALLY DEFERRED NORELY DISABLE); ALTER TABLE X1_tab ADD CONSTRAINT One_cnstrt UNIQUE(A1) DEFERRABLE INITIALLY IMMEDIATE RELY USING INDEX PCTFREE = 30 ENABLE VALIDATE; ALTER TABLE X1_tab MODIFY UNIQUE(A1) INITIALLY DEFERRED NORELY USING INDEX PCTFREE = 40 ENABLE NOVALIDATE; ALTER TABLE X1_tab MODIFY CONSTRAINT One_cnstrt INITIALLY IMMEDIATE RELY;
次のコマンドは、主キー制約が施行されるかどうか、およびその制約チェックが実行されたときの選択肢を示します。
CREATE TABLE T1_tab (A1 INT, B1 INT); ALTER TABLE T1_tab add CONSTRAINT P1_cnstrt PRIMARY KEY(a1) DISABLE; ALTER TABLE T1_tab MODIFY PRIMARY KEY INITIALLY IMMEDIATE USING INDEX PCTFREE = 30 ENABLE NOVALIDATE; ALTER TABLE T1_tab MODIFY PRIMARY KEY USING INDEX PCTFREE = 35 ENABLE; ALTER TABLE T1_tab MODIFY PRIMARY KEY ENABLE NOVALIDATE;
制約名は、複数のスキーマにわたって一意である必要があるため、表およびそのすべての制約の複製時に新しい表の制約名と元の表の制約名が競合すると、問題が発生します。また、制約をデフォルトのシステム生成名で作成した場合、後で、使用可能または使用禁止への設定が容易なように、覚えやすい制約名が必要になる場合があります。
制約の変更可能なプロパティの1つは、制約名です。次に、制約のシステム生成名を検索し、名前を変更する場合に使用するSQL*Plusスクリプトを示します。
prompt Enter table name to find its primary key: accept table_name select constraint_name from user_constraints where table_name = upper('&table_name.') and constraint_type = 'P'; prompt Enter new name for its primary key: accept new_constraint set serveroutput on declare -- USER_CONSTRAINTS.CONSTRAINT_NAME is declared as VARCHAR2(30). -- Using %TYPE here protects us if the length changes in a future release. constraint_name user_constraints.constraint_name%type; begin select constraint_name into constraint_name from user_constraints where table_name = upper('&table_name.') and constraint_type = 'P'; dbms_output.put_line('The primary key for ' || upper('&table_name.') || ' is: ' || constraint_name); execute immediate 'alter table &table_name. rename constraint ' || constraint_name || ' to &new_constraint.'; end; /
整合性制約は、施行する規則が業務に適応しなくなった場合、またはその制約が不要になった場合に削除します。整合性制約は、ALTER TABLEコマンドのDROP句を使用して削除します。たとえば、次の文は整合性制約を削除します。
ALTER TABLE Dept_tab
DROP UNIQUE (Dname);
ALTER TABLE Dept_tab
DROP UNIQUE (Loc);
ALTER TABLE Emp_tab
DROP PRIMARY KEY,
DROP CONSTRAINT Dept_fkey;
DROP TABLE Emp_tab CASCADE CONSTRAINTS;
一意キー、主キーおよび外部キーの各整合性制約を削除する場合は、いくつかの重要な問題および前提条件を把握しておく必要があります。一意キー制約および主キー制約は、通常、データベース管理者が管理します。
すべてのタイプの整合性制約の定義、使用可能、使用禁止および削除に関する一般的な情報については、「整合性制約の削除」の項で説明しました。この項では、特に外部キー整合性制約の問題に重点を置き、それらの情報を補足します。この整合性制約は、異なる表の列同士の関連を規定します。
依存する側の表と参照される側の表の対応する列は、同じデータ型である必要があります。列の名前は一致する必要はありません。
外部キーは、親表の主キーまたは一意キーを参照し、主キー制約および一意キー制約は索引を使用して施行されるため、複合外部キーは32列以内に制限されています。
外部キー制約（単一列または複合列）を定義するときに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);
ON DELETE SET NULLアクションを指定すると、親キーを参照するデータを削除できます。ただし、更新はできません。親キー内の参照データが削除されると、削除された親キー値に依存する子表内のすべての行の外部キーがNULLに設定されます。この参照アクションを指定するには、外部キー制約の定義にON DELETE SET NULLオプションを指定します。次に例を示します。
CREATE TABLE Emp_tab ( FOREIGN KEY (Deptno) REFERENCES Dept_tab ON DELETE SET NULL);
データ・ディクショナリには、整合性制約について次のビューがあります。
ALL_CONSTRAINTS
ALL_CONS_COLUMNS
USER_CONSTRAINTS
USER_CONS_COLUMNS
DBA_CONSTRAINTS
DBA_CONS_COLUMNS
これらのビューを問い合せて、制約の名前、影響がある列および制約の管理に有効な他の情報を検索できます。
次のCREATE TABLE文は、多くの整合性制約を定義します。
CREATE TABLE Dept_tab ( Deptno NUMBER(3) PRIMARY KEY, Dname VARCHAR2(15), Loc VARCHAR2(15), CONSTRAINT Dname_ukey UNIQUE (Dname, Loc), CONSTRAINT LOC_CHECK1 CHECK (Loc IN ('NEW YORK', 'BOSTON', 'CHICAGO'))); CREATE TABLE Emp_tab ( Empno NUMBER(5) PRIMARY KEY, Ename VARCHAR2(15) NOT NULL, Job VARCHAR2(10), Mgr NUMBER(5) CONSTRAINT Mgr_fkey REFERENCES Emp_tab ON DELETE CASCADE, Hiredate DATE, Sal NUMBER(7,2), Comm NUMBER(5,2), Deptno NUMBER(3) NOT NULL CONSTRAINT Dept_fkey REFERENCES Dept_tab);
次の問合せで、ユーザーがアクセス可能なすべての表に定義されているすべての制約がリストされます。
SELECT Constraint_name, Constraint_type, Table_name, R_constraint_name FROM User_constraints;
この項の最初の文の場合、次のリストが戻されます。
CONSTRAINT_NAME C TABLE_NAME R_CONSTRAINT_NAME --------------- - ---------- ----------------- SYS_C00275 P DEPT_TAB DNAME_UKEY U DEPT_TAB LOC_CHECK1 C DEPT_TAB SYS_C00278 C EMP_TAB SYS_C00279 C EMP_TAB SYS_C00280 P EMP_TAB MGR_FKEY R EMP_TAB SYS_C00280 DEPT_FKEY R EMP_TAB SYS_C00275
次のことに注意してください。
DNAME_UKEY）のものと、システム指定（たとえば、SYS_C00275）のものがあります。
CONSTRAINT_TYPE列に別々の文字で表示されます。次の表に、各制約タイプに対応する文字を示します。| 制約タイプ | 文字 |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
例1では、Cというタイプの制約がいくつか表示されています。どの制約がEMP_TAB表およびDEPT_TAB表のNOT NULL制約であり、CHECK制約であるかを区別するには、次の問合せを発行します。
SELECT Constraint_name, Search_condition FROM User_constraints WHERE (Table_name = 'DEPT_TAB' OR Table_name = 'EMP_TAB') AND Constraint_type = 'C';
この項の最初のCREATE TABLE文の場合、次のリストが戻されます。
CONSTRAINT_NAME SEARCH_CONDITION --------------- ---------------------------------------- LOC_CHECK1 loc IN ('NEW YORK', 'BOSTON', 'CHICAGO') SYS_C00278 ENAME IS NOT NULL SYS_C00279 DEPTNO IS NOT NULL
次の項目は、SEARCH_CONDITION列に明示的にリストされます。
次の問合せで、ユーザーがアクセス可能なすべての表に定義されている制約を構成するすべての列がリストされます。
SELECT Constraint_name, Table_name, Column_name FROM User_cons_columns;
この項の最初の文の場合、次のリストが戻されます。
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME --------------- ----------- --------------- DEPT_FKEY EMP_TAB DEPTNO DNAME_UKEY DEPT_TAB DNAME DNAME_UKEY DEPT_TAB LOC LOC_CHECK1 DEPT_TAB LOC MGR_FKEY EMP_TAB MGR SYS_C00275 DEPT_TAB DEPTNO SYS_C00278 EMP_TAB ENAME SYS_C00279 EMP_TAB DEPTNO SYS_C00280 EMP_TAB EMPNO
|
 Copyright © 2006 Oracle Corporation. All Rights Reserved. |
|