この章では、整合性制約によって、データベースに関連するビジネス・ルールがどのように規定され、表への無効なエントリがどのように防止されるかについて説明します。
この章の内容は、次のとおりです。
ビジネス・ルールでは、常に真(true)となる必要がある、または常に偽(false)となる必要がある条件および関係を定義します。たとえば、各企業では、給与、従業員番号、在庫追跡などに関して独自のポリシーを定義します。データベース管理者やアプリケーション開発者が決定した規則に準拠し、データ整合性を維持するのは重要なことです。
データベース・アプリケーションを設計する場合、開発者は、データベースに格納するデータの整合性を保証するための様々なオプションを使用できます。このオプションには次のものがあります。
トリガーされるストアド・データベース・プロシージャを使用して、ビジネス・ルールを規定します(「トリガーの概要」を参照)。
ストアド・プロシージャを使用してデータへのアクセスを完全に制御します(「サーバー側プログラミングの概要」を参照)。
データベース・アプリケーションのコードでビジネス・ルールを規定します。
列またはオブジェクト・レベルで定義される、データベースの値を制限する規則である、Oracle Databaseの整合性制約を使用します。
この章では、整合性制約の基本概念について説明します。
整合性制約は、SQLを使用して作成および削除されるスキーマ・オブジェクトです。データ整合性を維持するために、使用できない場合を除いて、整合性制約を使用するようにしてください。データ整合性を維持する方法として、整合性制約には、次のような利点があります。
宣言の容易さ
SQL文を使用して整合性制約を定義するため、追加のプログラミングなしに表を定義または変更できます。SQL文は記述が容易で、プログラミングのエラーを避けられます。
規則の集中化
整合性制約は表に対して定義され、データ・ディクショナリに格納されます(「データ・ディクショナリの概要」を参照)。したがって、すべてのアプリケーションから入力されるデータは、同じ整合性制約を遵守する必要があります。規則が表レベルで変化した場合も、アプリケーションを変更する必要はありません。また、データベースによってSQL文がチェックされる前に、アプリケーションがデータ・ディクショナリのメタデータを使用して、すぐにユーザーに違反を通知することもできます。
データをロードする場合の柔軟性
大量のデータをロードする場合は、パフォーマンスのオーバーヘッドを回避するために、整合性制約を一時的に無効にできます。データ・ロードが完了した後、整合性制約を再度有効にできます。
関連項目:
|
Oracle Databaseでは、表と列の両方のレベルで制約を適用できます。列または属性の定義の一部として指定される制約は、表内指定と呼びます。表定義の一部として指定される制約は、表外指定と呼びます。
キーという用語は、複数のタイプの整合性制約の定義で使用されます。キーとは、特定タイプの整合性制約の定義に含まれる列または列の集合です。キーによって、リレーショナル・データベースの表と列の間の関連が示されます。キーの中にある個々の値を、キー値と呼びます。
表5-1に、制約のタイプを示します。表内指定であることが必要なNOT NULL
を除き、どの制約も表内と表外のいずれかで指定できます。
表5-1 制約のタイプ
制約タイプ | 説明 | 関連項目 |
---|---|---|
|
指定した列でのNULLを含む行の挿入または更新を許可または禁止します。 |
|
一意キー |
|
|
主キー |
|
|
外部キー |
|
|
チェック |
|
|
|
|
REF制約の詳細は、『Oracle Databaseオブジェクト・リレーショナル開発者ガイド』 |
NOT
NULL
制約がある場合、表の列値にNULLを使用できません。NULLは、値がないことを示します。デフォルトでは、表のすべての列でNULLを使用できます。
NOT NULL
制約は、値が必須である列のためのものです。たとえば、hr.employees
表ではlast_name
列に値が必要です。姓なしで従業員の行を挿入しようとすると、次のエラーが発生します。
SQL> INSERT INTO hr.employees (employee_id, last_name) values (999, 'Smith'); . . . ERROR at line 1: ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."LAST_NAME")
NOT NULL
制約を持つ列は、行が1つも含まれていない表またはデフォルト値が指定された表にのみ追加できます。
関連項目:
|
一意キー制約では、列(または列の集合)のすべての値が一意である必要があります。一意キー制約が設定されていると、表の行では、列(一意キー)または列の集合(コンポジット一意キー)で重複する値を持つことはありません。
注意: キーという用語は、整合性制約で定義されている列のみを指します。データベースではキー列に対して索引を暗黙的に作成または再利用することで一意制約による規制を規定するため、一意キーという用語は一意キー制約または一意索引のシノニムとして誤用されることがあります。 |
一意キー制約は、任意の列に重複する値が格納されないようにする場合に適しています。一意制約は、(各表の行を一意に識別することが目的である)主キー制約とは異なり、通常は、一意であること以外の意味を持たない値が含まれています。一意キーの例には次のものがあります。
主キーが顧客番号である場合の、顧客電話番号
主キーが部門番号である場合の、部門名
例2-1に示すように、hr.employees
表のemail
列には一意キー制約が存在します。文の関連する部分は、次のとおりです。
CREATE TABLE employees ( ... , email VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL ... , CONSTRAINT emp_email_uk UNIQUE (email) ... );
例5-1
に示すように、emp_email_uk制約により、2人の従業員が同じ電子メール・アドレスを持つことはありません。
例5-1 一意制約
SQL> SELECT employee_id, last_name, email FROM employees WHERE email = 'PFAY'; EMPLOYEE_ID LAST_NAME EMAIL ----------- ------------------------- ------------------------- 202 Fay PFAY SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id) 1 VALUES (999,'Fay','PFAY',SYSDATE,'ST_CLERK'); . . . ERROR at line 1: ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated
NOT NULL
制約も定義されている場合を除き、一意キー制約では、NULLは常に許可されます。このため、一意キー制約とNOT NULL
制約の両方を持つ列が一般的に使用されます。これらの組合せにより、ユーザーは一意キーに必ず値を入力することになり、さらに新しい行データが既存の行データと競合することがなくなります。
主キー制約を設定すると、この制約の対象となる1つ以上の列グループの値によって、行が一意に識別されます。各表には1つの主キーを設定でき、この主キーによって行が識別され、行が重複しないことが保証されます。
主キーには、自然キーまたはサロゲート・キーを使用できます。自然キーは、表内の既存の属性から作成される、意味のある識別子です。たとえば、参照表の郵便番号を自然キーとして使用できます。これに対して、サロゲート・キーは、システムで生成され、値が増加する識別子で、この識別子によって表内での一意性が保たれます。通常、サロゲート・キーは順序によって生成されます。
Oracle Databaseでは、主キー制約の実装により、次のことが保証されます。
指定された列または列の集合の中に、2つの行が重複する値を持つことはありません。
主キー列では、NULLは許可されません。
主キーが必要となる典型的な状況は、従業員用の数値識別子です。各従業員は、一意のIDを持つ必要があります。従業員は、employees
表の1つの行によってのみ記述する必要があります。
例5-1では、既存の従業員が従業員IDとして202を持っており、従業員IDが主キーであることが示されています。次に示す例では、同じ従業員IDを持つ従業員、およびIDを持たない従業員を追加します。
SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id) 1 VALUES (202,'Chan','ICHAN',SYSDATE,'ST_CLERK'); . . . ERROR at line 1: ORA-00001: unique constraint (HR.EMP_EMP_ID_PK) violated SQL> INSERT INTO employees (last_name) VALUES ('Chan'); . . . ERROR at line 1: ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMPLOYEE_ID")
データベースにより、索引を持つ主キー制約が規定されます。通常、列に対して作成された主キー制約によって、暗黙的に一意索引とNOT NULL
制約が作成されます。この規則には、次の例外がある点に注意してください。
場合によっては、遅延可能制約を使用して主キーを作成する場合、生成された索引は一意ではありません。
主キー制約の作成時に使用できる索引がある場合、新しい索引を暗黙的に作成するのではなく、制約はこの索引を再使用します。
デフォルトでは、暗黙的に作成された索引の名前が、主キー制約の名前になります。索引にはユーザー定義名を指定することもできます。制約の作成に使用するCREATE TABLE
文またはALTER TABLE
文にENABLE
句を組み込んで、索引の記憶域オプションを指定できます。
関連項目: 表に主キー制約を追加する方法の詳細は、『Oracle Database 2日で開発者ガイド』および『Oracle Databaseアドバンスト・アプリケーション開発者ガイド』を参照してください。 |
2つの表に1つ以上の共通の列が含まれる場合、Oracle Databaseでは、参照整合性制約とも呼ばれる外部キー制約を介して2つの表の間の関係を規定できます。この制約では、制約が定義される列の各値に対して、他の表と列を指定するもう一方の列の値が一致する必要があります。参照整合性規則の例としては、既存の部門にのみ従事できる従業員があります。
表5-2に、参照整合性制約に関連する用語を示します。
表5-2 参照整合性制約の用語
図5-1に、employees.department_id
列の外部キーを示します。この列のすべての値が、departments.department_id
列の値と一致することが保証されます。このため、employees.department_id
列に間違った部門番号が存在することはありません。
関連項目: 表に外部キー制約を追加する方法の詳細は、『Oracle Database 2日で開発者ガイド』および『Oracle Databaseアドバンスト・アプリケーション開発者ガイド』を参照してください。 |
図5-2に、自己参照型整合性制約を示します。この場合、外部キーは同じ表の親キーを参照します。
図5-2では、参照型整合性制約によって、employees.manager_id
列のすべての値が、employees.employee_id
列の既存の値に対応するようになります。たとえば、従業員102の管理職がemployees
表に存在する必要があります。この制約によって、間違った従業員番号がmanager_id
列に存在する可能性がなくなります。
リレーショナル・モデルでは、外部キーの値は、参照先の主キーまたは一意キーの値と一致するか、NULLであることが可能です。たとえば、ユーザーは部門IDを指定することなくhr.employees
に行を挿入できます。
コンポジット外部キーのいずれかの列がNULLの場合、そのキーのNULL以外の部分は、親キーの対応部分と一致している必要はありません。
親キーを削除するとき、外部キーと親キーの間の関係が影響を及ぼします。たとえば、あるユーザーが部門のレコードの削除を試行すると、この部門の従業員のレコードに何が起こるかについて説明します。
親キーが変更された場合、参照整合性制約では、子表の依存行に対して実行される次のアクションを指定できます。
削除または更新に対するアクションなし
通常、結果が参照整合性に違反する場合、ユーザーは参照キー値を変更できません。たとえば、employees.department_id
がdepartments
に対する外部キーであり、従業員が特定の部門に属す場合、この部門の行の削除を試行すると、制約に対する違反となります。
連鎖的な削除
参照キー値を含む行が削除された場合、子表のうち依存している外部キー値を含むすべての行も削除されます(DELETE CASCADE
)。たとえば、departments
の行を削除すると、この部門のすべての従業員の行が削除されます。
NULLを設定する削除
参照キー値を含む行が削除された場合に、子表のうち依存している外部キー値を含むすべての行の値がNULLに設定されます(DELETE SET NULL
)。たとえば、部門の行を削除すると、この部門のすべての従業員に関してdepartment_id
列値がNULLに設定されます。
表5-3に、親表のキー値および子表の外部キー値に対する異なる参照アクションごとに可能なDML文の概要を示します。
表5-3 UPDATE NO ACTIONとDELETE NO ACTIONで許可されるDML文
DML文 | 親表に対して発行 | 子表に対して発行 |
---|---|---|
|
親キー値が一意であれば常に発行できます。 |
外部キーの値が親キーに存在するか、外部キーの一部またはすべてがNULLの場合にのみ発行できます。 |
|
文の実行後に、参照される親キー値のない行が子表内に残らない場合は発行できます。 |
文の実行後も新しい外部キー値によって参照キー値が参照される場合は発行できます。 |
|
子表のどの行も親キー値を参照していない場合は発行できます。 |
常に発行可能 |
|
常に発行可能 |
常に発行可能 |
|
常に発行可能 |
常に発行可能 |
関連項目: ON DELETE句の詳細は、『Oracle Database SQL言語リファレンス』 を参照してください。 |
一般的に、ヒープ構成表には外部キーの索引付けをお薦めします。パーティション化されていない表での例外は、対応する一意キーまたは主キーの更新や削除が発生しないことが確実な場合です。
注意: 索引構成表および表クラスタなどのヒープ以外のデータ構造には、追加の考慮事項が適用されます。 |
子表で外部キーの索引付けを行うと、次の利点があります。
子表に対する完全表ロックが防止されます。かわりに、データベースは索引に対する行ロックを取得します。
子表の全表スキャンを行う必要がなくなります。例として、ユーザーがdepartments
表から部門10のレコードを削除する場合を想定します。employees.department_id
が索引付けされていない場合、データベースはemployees
をスキャンして、部門10に従業員が存在するかどうかを判断する必要があります。
列または列の集合に対するチェック制約では、すべての行について、指定した条件がTRUEまたはUNKNOWNであることが必要です。DMLの結果で制約の条件がFALSEに評価される場合、そのSQL文はロールバックされます。
チェック制約の主な利点は、特殊な整合性規則を規定できることです。たとえば、チェック制約を使用すると、hr.employees
表に次の規則を規定できます。
salary
列の値は10000以下である必要があります。
commission
列の値は、salaryの値以下である必要があります。
次の例では、employees
に最大給与制約を作成し、最大値を超える給与を含む行を文で挿入すると、どのようなことが起こるかを示します。
SQL> ALTER TABLE employees ADD CONSTRAINT max_emp_sal CHECK (salary < 10001); SQL> INSERT INTO employees (employee_id,last_name,email,hire_date,job_id,salary) 1 VALUES (999,'Green','BGREEN',SYSDATE,'ST_CLERK',20000); . . . ERROR at line 1: ORA-02290: check constraint (HR.MAX_EMP_SAL) violated
1つの列に、定義された列を参照する複数のチェック制約を設定できます。たとえば、salary
列には、10000より大きい値を禁止する1つの制約と、500未満の値を禁止する別の制約を設定できます。
単一の列に対して複数のチェック制約が存在する場合は、その目的が競合しないように設計する必要があります。また、条件は特定の順序では評価されません。データベースでは、チェック条件が矛盾しないかどうかは検証されません。
関連項目: チェック制約の制限の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
制約定義の一部として、Oracle Databaseがいつどのようにして制約を規定するかを指定できます。これにより、制約状態が決定します。
データベースでは、制約が既存のデータまたは将来のデータに適用されるかどうかを指定できます。制約が使用可能である場合、データベースは、入力または更新の時点での新規データをチェックします。制約に準拠しないデータをデータベースに入力することはできません。たとえば、employees.department_id
に対してNOT NULL
制約を使用可能にすると、将来のすべての行が部門IDを持ちます。制約が使用禁止である場合、表には制約に違反する行を入力できます。
既存のデータを検証する(VALIDATE
)または検証しない(NOVALIDATE
)ように制約を設定できます。VALIDATE
を指定すると、既存のデータが制約に準拠している必要があります。たとえば、employees.department_id
に対してNOT NULL
制約を使用可能にし、この制約をVALIDATE
に設定すると、既存のすべての行が部門IDを持つことをチェックできます。NOVALIDATE
を指定すると、既存のデータは制約に準拠する必要はありません。
VALIDATE
およびNOVALIDATE
の動作は、常に、制約が使用可能であるか使用禁止であるかどうかによって異なります。表5-4に、その関係の概要を示します。
表5-4 変更されたデータおよび既存のデータのチェック
変更されたデータ | 既存のデータ | 概要 |
---|---|---|
|
|
既存のデータと将来のデータは制約に準拠する必要があります。データが入力されている表に新しい制約を適用しようとすると、既存の行がその制約に違反する場合、エラーになります。 |
|
|
データベースは制約をチェックしますが、制約はすべての行に関してTRUEである必要はありません。このため、既存の行が制約に違反している可能性はありますが、新しい行または変更された行は規則に準拠する必要があります。 |
|
|
データベースでは制約が使用禁止になり、索引が削除されて、制約の対象となる列への変更が禁止されます。 |
|
|
制約はチェックされず、TRUEでなくてもかまいません。 |
関連項目: 制約の状態の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
あらゆる制約は、遅延不可状態(デフォルト)または遅延可能状態のいずれかにあります。この状態によって、Oracle Databaseが妥当性を確認するためにどの時点で制約をチェックするかが決まります。次の図は、遅延可能制約のオプションを示します。
制約が遅延不可である場合、Oracle Databaseは、制約の妥当性チェックをトランザクションの最後まで遅延することはありません。かわりに、データベースでは各文の最後で制約をチェックします。制約に違反している場合、その文はロールバックします。
たとえば、employees.last_name
列用に遅延不可のNOT NULL
制約を作成するとします。ユーザーが姓のない行を挿入しようとすると、NOT NULL
制約に違反しているため、データベースはただちに文をロールバックします。行は挿入されません。
遅延可能制約を使用すると、トランザクションでSET CONSTRAINT
句を使用して、COMMIT
文が発行されるまでこの制約のチェックを遅延させることができます。制約に違反している可能性があるデータベースを変更する場合、この設定により、すべての変更が完了するまで制約を事実上無効にすることができます。
データベースが遅延可能制約をチェックする時点に関して、デフォルト動作を設定できます。次のいずれかの属性を指定できます。
INITIALLY IMMEDIATE
データベースは、各文の実行直後に制約をチェックします。制約に違反している場合、データベースは文をロールバックします。
INITIALLY DEFERRED
データベースは、COMMIT
が発行された時点で制約をチェックします。制約に違反している場合、データベースはトランザクションをロールバックします。
employees.last_name
に対する遅延可能なNOT NULL
制約がINITIALLY DEFERRED
に設定されているとします。ユーザーは100個のINSERT
文を含むトランザクションを作成し、その一部にはlast_name
としてNULL値があります。ユーザーがコミットしようとすると、データベースは100個すべての文をロールバックします。ただし、この制約がINITIALLY IMMEDIATE
に設定されている場合、データベースはトランザクションをロールバックしません。
制約によってアクションが発生する場合は、遅延制約か即時制約かに関係なく、このアクションはアクションを発生させた文の一部とみなされます。たとえば、departments
の行を削除すると、削除された部門の行を参照するemployees
のすべての行が削除されます。この場合、employees
からの削除は、departments
に対して実行されたDELETE
文の一部とみなされます。
関連項目: 制約属性とそのデフォルト値の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
Oracle Databaseが制約のチェックをどの時点で実行するかを説明するため、いくつかの例を示します。この例では、次のような状況を想定します。
図5-2
に、employees表の構造を示します。
自己参照型制約によって、manager_id
列のエントリがemployee_id
列の値に依存しています。
employees
表に最初の行を挿入する場合を考えます。現在は行が存在しないため、manager_id
列の値がemployee_id
列の既存の値を参照できない場合に行を入力する方法を考えなくてはなりません。次の方法が考えられます。
manager_id
列にNOT
NULL
制約が定義されていない場合には、第1行のmanager_id
列にNULLを入力できます。
外部キーにはNULLが許されるため、この行は表に挿入されます。
同じ値をemployee_id
列およびmanager_id
列に入力できます。これにより、その従業員が管理職であることを示します。
このことから、Oracle Databaseが文の実行完了後に制約チェックを実行することがわかります。親キーと外部キーに同じ値を指定した行の入力を許可するために、データベースは、文を実行(つまり、新しい行を挿入)してから、その新しい行のmanager_id
に対応するemployee_id
のある行がその表に含まれているかどうかをチェックします。
SELECT
文のネストを伴うINSERT
文など、複数行を挿入するINSERT
文により、相互に参照しあう行を挿入できます。
たとえば、第1行には従業員IDとして200、管理職IDとして300が含まれており、第2行には従業員IDとして300、管理職IDとして200が含まれているとします。制約チェックは文の実行が完了するまで遅延されています。すべての行が挿入されてから、制約違反がないかどうかすべての行が調べられます。
デフォルト値は、文の解析前にINSERT
文の一部として組み込まれます。このため、デフォルトの列値はすべての整合性制約チェックの対象になります。
同じ自己参照型の整合性制約に関して、別の使用例を考えます。会社が買収された場合です。この買収に伴い、すべての従業員番号の現在の設定値に5000を加算して、新しい会社の従業員番号と調和させる必要があります。管理職番号は実際には従業員番号であるため、管理職番号にも5000を加算する必要があります(図5-3を参照)。
次のSQL文を実行すると値を更新できます。
UPDATE employees SET employee_id = employee_id + 5000, manager_id = manager_id + 5000;
制約は、各manager_id
値がemployee_id
値と一致するかどうかを検証するように定義されていますが、データベースでは文の完了後に制約チェックが効率的に実行されるため、前述の文は有効です。図5-4に、データベースでは、制約をチェックする前に、SQL文全体のアクションが実行されることを示します。
この項の例は、INSERT
文とUPDATE
文を実行した場合の制約チェックのメカニズムを示していますが、データベースでは同じメカニズムがすべてのタイプのDML文に使用されます。同じメカニズムが、自己参照型制約のみでなく、すべてのタイプの制約に使用されます。