constraint

目的

constraintを使用すると、整合性制約(データベース内の値を制限する規則)を定義できます。Oracle Databaseでは、6つの制約を作成し、それを2つの方法で宣言することができます。

次に、6つの整合性制約について簡単に説明します。詳細は、「セマンティクス」を参照してください。

• NOT NULL 制約は、データベースの値がNULLになることを禁止します。

• 一意制約は、複数の行が同じ列または列の組合せで同じ値を持つことを禁止しますが、一部の値がNULLになることを許可します。

• 主キー制約は、1つの宣言でNOT NULL制約と一意制約を組み合せたものです。同じ列または列の組合せで、複数の行が同じ値を持つことを禁止し、値がNULLであることを禁止します。

• 外部キー制約は、ある表の値が別の表の値と一致することを必要とします。

• CHECK制約は、データベースの値が、指定された条件を満たすことを必要とします。

• REF列は、定義上は別のオブジェクト型またはリレーショナル表の中のオブジェクトを参照します。REF制約を使用すると、REF列と参照先のオブジェクトの関係をさらに詳しく指定できます。

制約は、次の2つの構文で定義できます。

• 個々の列または属性の定義の一部として定義できます。これを、表内指定といいます。

• 表定義の一部として定義できます。これを、表外指定といいます。

NOT NULL制約は、表内に宣言する必要があります。その他のすべての制約は、表内または表外に宣言できます。

制約句は、次の文に指定できます。

• CREATE TABLE(「CREATE TABLE」を参照)

• ALTER TABLE(「ALTER TABLE」を参照)

• CREATE VIEW(「CREATE VIEW」を参照)

• ALTER VIEW(「ALTER VIEW」を参照)

ビュー制約

Oracle Databaseでは、ビュー制約を適用していません。ただし、実表に対する制約によってビューに制約を適用できます。

ビューには、一意制約、主キー制約および外部キー制約のみを指定でき、これらの制約はDISABLE NOVALIDATEモードのみでサポートされています。オブジェクト列の属性にビュー制約を定義することはできません。

関連項目:

ビュー制約の詳細は、「ビュー制約」を参照してください。DISABLE NOVALIDATEモードの詳細は、「DISABLE句」を参照してください。

外部表の制約

外部表には、NOT NULL、一意制約、主キー制約および外部キー制約のみ指定できます。一意制約、主キー制約および外部キーの制約は、RELY DISABLEモードでのみサポートされます。

関連項目:

RELYおよびDISABLEの詳細は、「DISABLE句」を参照してください。

前提条件

制約を定義する文を発行できる権限が必要です。

外部キー制約を作成する場合は、この条件に加えて、親表またはビューが自分のスキーマ内に設定されている必要があります。設定されていないかぎり、親表またはビューの参照キー列に対するREFERENCES権限が必要です。

構文

constraint::=

図constraint.epsの説明

(inline_constraint::=、out_of_line_constraint::=、inline_ref_constraint::=、out_of_line_ref_constraint::=)

inline_constraint::=

図inline_constraint.epsの説明

(references_clause::=)

precheck_state::=

図precheck_state.epsの説明

out_of_line_constraint::=

図out_of_line_constraint.epsの説明

(references_clause::=、constraint_state::=)

inline_ref_constraint::=

図inline_ref_constraint.epsの説明

(references_clause::=、constraint_state::=)

out_of_line_ref_constraint::=

図out_of_line_ref_constraint.epsの説明

(references_clause::=、constraint_state::=)

references_clause::=

図references_clause.epsの説明

constraint_state::=

図constraint_state.epsの説明

(using_index_clause::=、exceptions_clause::=)

using_index_clause::=

図using_index_clause.epsの説明

(create_index::=、index_properties::=)

index_properties::=

図index_properties.epsの説明

(global_partitioned_index::=、local_partitioned_index::=(CREATE INDEXの一部)、index_attributes::=::=。INDEXTYPE IS ...句は、制約を定義すると無効になります。)

index_attributes::=

図index_attributes.epsの説明

(physical_attributes_clause::=、logging_clause::=、index_compression::=、partial_index_clause::=(すべてCREATE INDEXの一部)、parallel_clauseはusing_index_clauseではサポートされません。)

exceptions_clause::=

図exceptions_clause.epsの説明

セマンティクス

この項では、constraintのセマンティクスについて説明します。詳細は、表またはビューの制約を定義または再定義するSQL文の説明を参照してください。

Oracle Databaseでは、ユーザー定義オブジェクト、ネストした表、VARRAY、REFまたはLOB型の列または属性に対して制約を使用することはできません。ただし、次の2つの例外があります。

• NOT NULL制約は、ユーザー定義オブジェクト、VARRAY、REFまたはLOB型の列や属性に使用できます。

• NOT NULL、外部キーおよびREF制約は、REF型の列に使用できます。

CONSTRAINT constraint_name

制約名を指定します。名前は、「データベース・オブジェクトのネーミング規則」に指定されている要件を満たしている必要があります。この識別子を指定しない場合、SYS_Cnの形式で名前が生成されます。整合性制約の名前と定義は、USER_、ALL_およびDBA_CONSTRAINTSデータ・ディクショナリ・ビュー(それぞれCONSTRAINT_NAME列およびSEARCH_CONDITION列)に格納されます。

関連項目:

データ・ディクショナリ・ビューの詳細は、『Oracle Databaseリファレンス』を参照してください。

NOT NULL制約

NOT NULL制約は、列にNULLが含まれることを禁止します。NULLキーワード自体は、実際に整合性制約を定義するものではありませんが、これを指定すると、列にNULLが含まれることが許可されます。NOT NULLおよびNULLは、表内指定で定義する必要があります。NOT NULLまたはNULLを指定しない場合、NULLがデフォルトになります。

NOT NULL制約は、XMLType列およびVARRAY列で表内指定できる唯一の制約です。

NOT NULL制約を満たすには、表のすべての行がその列の値を持つ必要があります。

ノート:

Oracle Databaseでは、すべてのキー列がNULLの表の行には索引を付けません(ただし、ビットマップ索引の場合を除きます)。このため、表のすべての行に索引を付けるには、1つ以上の索引キー列にNOT NULL制約を指定するか、ビットマップ索引を作成する必要があります。

NOT NULL制約の制限事項

NOT NULL制約には、次の制限事項があります。

• ビュー制約にはNULLまたはNOT NULLを指定できません。

• オブジェクト属性にはNULLまたはNOT NULLを指定できません。そのかわりに、IS [NOT] NULL条件でCHECK制約を使用してください。

関連項目:

「属性レベル制約の例」および「NOT NULLの例」を参照してください。

一意制約

一意制約は、列を一意キーとして指定します。複合一意キーは、列の組合せを一意キーとして指定します。一意制約を表内に定義する場合に必要となるのは、UNIQUEキーワードのみです。一意制約を表外に定義する場合は、1つ以上の列も指定する必要があります。複合一意キーは、表外に定義する必要があります。

一意制約を満たすには、表の中の2つの行が一意キーに対して同じ値を持たないようにする必要があります。ただし、単一の列で構成される一意キーの場合は、複数のNULLを持つことができます。複合一意キーを満たすには、表またはビューの2つの行がキー列に対して同じ組合せの値を持たないようにする必要があります。すべてのキー列に対してNULLを持つ行は、自動的にその制約を満たすことになります。ただし、1つ以上のキー列に対してNULLを持ち、その他のキー列に対して同じ組合せの値を持つ2つの行は、制約に違反します。

一意制約は、そのキー列の宣言された照合に依存します。詳細は、「制約の照合依存」を参照してください。

Oracleでは、1つ以上の列に一意制約を指定すると、暗黙的に一意キーに索引が作成されます。問合せのパフォーマンスを目的に一意性を定義する場合は、かわりにCREATE UNIQUE INDEX文を使用して明示的に一意索引を作成することをお薦めします。また、CREATE UNIQUE INDEX文で、条件付きの一意制約を定義する一意のファンクション索引を作成することもできます。詳細は、「条件付き一意性を定義するためのファンクション索引の使用方法: 例」を参照してください。

使用可能な一意制約を拡張データ型の列に指定すると、Oracleがその使用可能な制約の一意性を適用するための索引を作成しようとしたときに、「最大キー長を超過しました」というエラーが通知されることがあります。この問題を回避する方法の詳細は、「拡張データ型の列に対する索引の作成」を参照してください。

一意制約の制限事項

一意制約には、次の制限事項があります。

• 一意キー列は、LOB、LONG、LONG RAW、VARRAY、NESTED TABLE、OBJECT、REF、TIMESTAMP WITH TIME ZONEまたはユーザー定義型を含むことができません。ただし、一意キーはTIMESTAMP WITH LOCAL TIME ZONEの列を含むことができます。

• 1つの複合一意キーは、33以上の列を持つことはできません。

• 同一の列または列の組合せを一意キーと主キーの両方には指定できません。

• 継承階層でサブビューを作成する場合は、一意キーを指定できません。一意キーは、トップレベル(ルート)のビューのみに指定できます。

• 外部表の一意制約を指定する場合は、RELYおよびDISABLE制約状態を指定する必要があります。詳細は、「外部表の制約」を参照してください。

関連項目:

「一意キーの例」および「複合一意キーの例」を参照してください。

主キー制約

主キー制約は、列を表またはビューの主キーとして指定します。複合主キーは、列の組合せを主キーとして指定します。主キー制約を表内に定義する場合に必要となるのは、PRIMARY KEYキーワードのみです。主キー制約を表外に定義する場合は、1つ以上の列も指定する必要があります。複合主キーは表外に定義する必要があります。

主キー制約を満たすには:

• 主キーの値が、表の中の複数行に存在することはできません。

• 主キーを構成する列に、NULLを持たせることはできません。

主キー制約を作成する場合

• Oracle Databaseでは、主キー制約を適用する前に既存の索引に一意の値の集合が含まれる場合、その索引が使用されます。既存の索引は、一意であると定義されている場合も、一意でないと定義されている場合もあります。DML操作を実行すると、この既存の索引を使用して主キー制約が適用されます。

• 既存の索引を使用できない場合は、一意索引が生成されます。

主キー制約を削除する場合

• 既存の索引を使用して主キーが作成された場合、索引は削除されません。

• システム生成の索引を使用して主キーが作成された場合、索引は削除されます。

拡張データ型の列を使用可能な主キーとして指定すると、Oracleがその使用可能な制約の一意性を適用するための索引を作成しようとしたときに「最大キー長を超過しました」というエラーが通知されることがあります。この問題を回避する方法の詳細は、「拡張データ型の列に対する索引の作成」を参照してください。

主キー制約は、そのキー列の宣言された照合に依存します。詳細は、「制約の照合依存」を参照してください。

主キー制約の制限事項

主キー制約には、次の制限事項があります。

• 表またはビューには、主キーを1つのみ指定できます。

• 主キー列は、LOB、LONG、LONG RAW、VARRAY、NESTED TABLE、BFILE、REF、TIMESTAMP WITH TIME ZONEまたはユーザー定義型を含むことができません。ただし、主キーはTIMESTAMP WITH LOCAL TIME ZONEの列を含むことができます。

• 主キーのサイズは、1データベース・ブロックを超えることはできません。

• 1つの複合主キーは、33以上の列を持つことはできません。

• 同一の列または列の組合せを一意キーと主キーの両方には指定できません。

• 継承階層でサブビューを作成する場合は、主キーを指定できません。主キーは、トップレベル(ルート)のビューのみに指定できます。

• 外部表の主キー制約を指定する場合は、RELYおよびDISABLE制約状態を指定する必要があります。詳細は、「外部表の制約」を参照してください。

関連項目:

「主キーの例」および「複合主キーの例」を参照してください。

外部キー制約

外部キー制約(参照整合性制約ともいう)は、列を外部キーとして指定し、その外部キーと指定した主キーまたは一意キー(参照キー)との関係を設定します。複合外部キーは、列の組合せを外部キーとして指定します。

外部キーを持つ表またはビューを子オブジェクトといい、参照キーを持つ表またはビューを親オブジェクトといいます。外部キーと参照キーを、同一の表またはビューに設定することができます。この場合、親表と子表は同一の表になります。親表または親ビューのみを指定して、列名を指定しない場合、外部キーは自動的に親表または親ビューの主キーを参照します。外部キーと参照キーの対応する列は、同じ順序、データ型および宣言された照合で構成されている必要があります。

外部キー制約は、参照先の主キー列または一意キー列の宣言された照合に依存します。詳細は、「制約の照合依存」を参照してください。

単一キー列の外部キー制約は、表内または表外に定義できます。複合外部キーと属性の外部キーは、表外に指定します。

複合外部キー制約を満たすには、複合外部キーが親表または親ビューの複合一意キーまたは複合主キーを参照するか、外部キーの1つ以上の列の値がNULLである必要があります。

外部キーと主キーの両方、または外部キーと一意キーの両方に、同一の列または列の組合せを指定できます。また、外部キーとクラスタ・キーの両方にも同じ列または列の組合せを指定できます。

1つの表またはビューで複数の外部キーを定義できます。また、1つの列が複数の外部キーを構成することもできます。

外部キー制約の制限事項

外部キー制約には、次の制限事項があります。

• 外部キー列は、LOB、LONG、LONG RAW、VARRAY、NESTED TABLE、BFILE、REF、TIMESTAMP WITH TIME ZONEまたはユーザー定義型を含むことができません。ただし、主キーはTIMESTAMP WITH LOCAL TIME ZONEの列を含むことができます。

• 親表または親ビューの参照一意制約または参照主キー制約が事前に定義されている必要があります。

• 1つの複合外部キーは、33以上の列を持つことはできません。

• 子表と親表は、同一データベース上に設定されている必要があります。分散データベースのノード間の参照整合性制約を使用可能にする場合、データベース・トリガーを使用する必要があります。「CREATE TRIGGER」を参照してください。

• 子オブジェクトと親オブジェクトのどちらかがビューの場合、ビュー制約のすべての制限事項が制約に適用されます。「ビュー制約」を参照してください。

• AS subquery句を含むCREATE TABLE文には、外部キー制約を定義できません。そのかわりに、制約を指定せずに表を作成し、後でALTER TABLE文を使用してその制約を追加できます。

• 表に外部キーが含まれ、外部キーの親が索引構成表の場合は、別のセッションが親表のキー以外の列を更新中のとき、外部キーを含む行を更新するセッションがハングすることがあります。

• 外部表の外部キー制約を指定する場合は、RELYおよびDISABLE制約状態を指定する必要があります。詳細は、「外部表の制約」を参照してください。

関連項目:

• 制約の使用の詳細は、『Oracle Database開発ガイド』を参照してください。

• 「外部キー制約の例」および「複合外部キー制約の例」を参照してください。

references_clause

外部キー制約はreferences_clause構文を使用します。外部キー制約を表内に指定する場合に必要となるのは、references_clauseのみです。外部キー制約を表外に指定する場合は、FOREIGN KEYキーワードと1つ以上の列も指定する必要があります。

ON DELETE句

ON DELETE句を指定すると、参照主キーまたは参照一意キーの値を削除した場合に参照整合性がどのように自動処理されるかを指定できます。この句を指定しない場合、子表に依存する行を持つ親表の中の参照キーの値は削除できません。

• 依存する外部キーの値を削除する場合は、CASCADEを指定します。

• 依存する外部キーの値をNULLに変換する場合は、SET NULLを指定します。仮想列の値は直接更新できないため、仮想列に対してこの句を指定することはできません。仮想列が導出される値を更新する必要があります。

ON DELETEの制限事項

この句は、ビュー制約に対して指定できません。

関連項目:

「ON DELETEの例」

CHECK制約

CHECK制約によって、表の各行に必要な条件を指定できます。制約を満たすためには、表のそれぞれの行が、その条件に対してTRUEまたは不明(NULLのため)のいずれかである必要があります。特定の行に対するCHECK制約条件が評価される場合、条件にある列名に、その行の列値が適用されます。

CHECK制約の表内指定と表外指定の構文は同じです。ただし、表内指定では現在定義されている列(または、オブジェクト列の場合は列の属性)のみを参照でき、表外指定では複数の列または属性を参照できます。

Oracleでは、CHECK制約の条件が相互に排他的かどうかは検証しません。このため、1つの列に対して複数のCHECK制約を作成する場合は、制約の用途が矛盾しないように注意する必要があります。また、条件の評価について特別な順序を想定しないでください。

外部JSONスキーマ・バリデータを使用してデータベースの外部で制約を検証する場合は、事前チェック状態がPRECHECKのチェック制約を指定できます。

JSONスキーマ・ボキャブラリで同等の条件を持つCHECK制約で使用されるSQL条件がサポートされています。

既存の制約状態ENABLEおよびVALIDATEとともにPRECHECKを同時に指定できます。

PRECHECKまたはNOPRECHECKを明示的に指定しない場合、Oracleは、チェック制約をJSONスキーマとして表すことができるかどうかに基づいて、PRECHECKまたはNOPRECHECKの値を自動的に設定します。

事前チェック状態は、既存の制約状態から独立しています。既存の制約とともに使用して、JSONスキーマを使用してデータベースの外部で制約を事前検証できることを示すことができます。

PRECHECK制約状態を削除するには、ALTER TABLE MODIFY CONSTRAINTを使用してNOPRECHECKに設定します。

CHECK制約の条件がNLS_DATE_FORMATなどのNLSパラメータに依存する場合、条件は、セッション値ではなくパラメータのデータベース値を使用して評価されます。NLSパラメータのデータベース値は、データ・ディクショナリ・ビューNLS_DATABASE_PARAMETERSにあります。これらの値はDDL文CREATE DATABASEによってデータベースに関連付けられ、その後変わることはありません。

CHECK制約の制限事項

CHECK制約には、次の制限事項があります。

• ビューに対しては、CHECK制約を指定できません。ただし、WITH CHECK OPTION句を使用してビューを定義することは可能です。これは、ビューにCHECK制約を指定することと同じです。

• CHECK制約の条件では、その表の中のすべての列を参照できますが、他の表の列は参照できません。

• CHECK制約の条件は、次の構造を持つことができません。

  • 副問合せおよびスカラー副問合せ式

  • 決定的でないファンクションへのコール(CURRENT_DATE、CURRENT_TIMESTAMP、DBTIMEZONE、LOCALTIMESTAMP、SESSIONTIMEZONE、SYSDATE、SYSTIMESTAMP、UID、USERおよびUSERENV)

  • ユーザー定義ファンクションへのコール

  • REF列の参照解除(DEREFファンクションを使用する場合など)

  • ネストした表の列または属性

  • 疑似列CURRVAL、NEXTVAL、LEVELまたはROWNUM

  • 完全に指定されていない日付定数

  • 外部表にはCHECK制約を指定できません。

関連項目:

• その他の詳細および構文は、「条件」を参照してください。

• 「CHECK制約の例」および「属性レベル制約の例」を参照してください。

• JSONスキーマを使用したPRECHECK

• CHECK制約の例

REF制約

REF制約を使用すると、REF型の列とそれが参照するオブジェクトとの関係を指定できます。

ref_constraint

REF制約は、ref_constraint構文を使用します。REF制約は表内または表外に定義します。表外指定の場合は、指定しているREF列または属性を指定する必要があります。

• ref_columnには、オブジェクトまたはリレーショナル表のREF列の名前を指定します。

• ref_attributeには、リレーショナル表のオブジェクト列内の埋込みREF属性を指定します。

表内指定と表外指定のどちらでも、有効範囲制約、ROWID制約または参照整合性制約をREF列に定義できます。

REF列の有効範囲表または参照表が、主キーに基づくオブジェクト識別子を持っている場合、そのREF列はユーザー定義REF列です。

関連項目:

• REFデータ型の詳細は、『Oracle Databaseオブジェクト・リレーショナル開発者ガイド』を参照してください。

• 「外部キー制約」および「REF制約の例」を参照してください。

REF列の有効範囲制約

表がREF列を持つ場合は、この列のそれぞれのREF値が別のオブジェクト表内の行を参照する場合があります。SCOPE句は、参照の有効範囲を1つの表scope_tableに制限します。REF列または属性の値はscope_table内のオブジェクトを指し、その場所にREF列と同じ型のオブジェクト・インスタンスが格納されます。

REF列内の参照の有効範囲を1つの表に制限する場合に、SCOPE句を指定します。この句を指定するには、scope_tableが自分のスキーマ内にあるか、またはscope_tableに対するREADまたはSELECT権限あるいはREAD ANY TABLEまたはSELECT ANY TABLEシステム権限が必要です。REF列ごとに有効範囲表を1つのみ指定できます。

有効範囲制約の制限事項

有効範囲制約には、次の制限事項があります。

• 表が空でない場合は、有効範囲制約を既存の列に追加できません。

• VARRAY列のREF要素に対しては有効範囲制約を指定できません。

• AS subqueryを指定し、この副問合せがユーザー定義REFデータ型を戻す場合、この句を指定する必要があります。

• 有効範囲制約を、後でREF列から削除することはできません。

• 外部表には有効範囲制約を指定できません。

REF列のROWID制約

WITH ROWIDを指定して、ref_columnまたはref_attributeのREFの値とともにROWIDを格納します。ROWIDとともにREF値を格納した場合、参照解除操作のパフォーマンスは向上しますが、使用する領域も多くなります。デフォルトのREF値の記憶域では、ROWIDは格納されません。

関連項目:

参照解除の例は、「DEREF」を参照してください。

ROWID制約の制限事項

ROWID制約には、次の制限事項があります。

• VARRAY列のREF要素に対してはROWID制約を定義できません。

• ROWID制約を、後でREF列から削除することはできません。

• REF列または属性の範囲が限定される場合、この句は無視され、ROWIDはREFとともには格納されません。

• 外部表にはROWID制約を指定できません。

REF列の参照整合性制約

ref_constraint構文のreferences_clauseを使用すると、外部キー制約をREF列に定義できます。また、この句はREF列や属性の有効範囲を参照表に暗黙的に制限します。ただし、REF列以外の外部キー制約が親表の実際の列を参照することに対し、REF列の外部キー制約は親表の暗黙のオブジェクト識別子を参照します。

制約名を指定しない場合、制約に対するシステム名がSYS_Cnという形式で生成されます。

範囲が限定されている既存のREF列に参照整合性制約を追加する場合、参照表は、REF列の有効範囲表と同じである必要があります。後で参照整合性制約を削除する場合、REF列の範囲が参照表に限定されたままになります。

他の型の列に対する外部キー制約と同様に、表内で宣言する場合に必要となるのはreferences_clauseのみです。表外で宣言する場合は、FOREIGN KEYキーワード、および1つ以上のREF列または属性も指定する必要があります。

関連項目:

オブジェクト識別子の詳細は、『Oracle Databaseオブジェクト・リレーショナル開発者ガイド』を参照してください。

REF列の外部キー制約の制限事項

REF列における外部キー制約には、次の追加の制限事項があります。

• 範囲が限定されていない既存のREF列に参照整合性制約を追加する場合、有効範囲制約が暗黙的に追加されます。したがって、有効範囲制約に適用されるすべての制限事項は、この場合にも適用されます。

• references_clause内のオブジェクト名の後には列を指定できません。

制約の照合依存

Oracle Database 12cリリース2 (12.2)以降、主キー制約、一意制約および外部キー制約はそのキー列の宣言された照合に依存します。新しい行または更新された行の主キーまたは一意キーの文字列値は、キー列の宣言された照合を使用して既存の行の値と比較されます。たとえば、キー列の宣言された照合が大/小文字を区別しない照合BINARY_CIである場合、新しいキー列の値が既存のキー値と大/小文字のみ異なるときは、新しい行または更新された行が拒否される可能性があります。照合BINARY_CIでは、大/小文字のみ異なる文字値は同じ値として処理されます。

外部キーの文字列値は、親キー列の宣言された照合を使用して親の主キー列または一意キー列の値と比較されます。たとえば、キー列の宣言された照合が大/小文字を区別しない照合BINARY_CIである場合、大/小文字のみ異なる値が存在するときは、対応する外部キー値について同じ親キー値がなくても、新しい子の行または更新された子の行が受け入れられる可能性があります。

外部キー列の宣言された照合は、対応する親キー列の照合と同じである必要があります。

制約のコンポジット・キーの列には、異なる宣言された照合を使用できます。

制約のキー列の宣言された照合が疑似照合の場合、制約では、照合BINARYの対応する変形が使用されます。制約は静的であり、疑似照合が依存するセッションNLSパラメータに依存できないため、疑似照合を直接使用して制約の値を比較することはできません。そのため、次のようになります。

• 疑似照合USING_NLS_COMP、USING_NLS_SORTおよびUSING_NLS_SORT_CSでは、照合BINARYが使用されます。

• 疑似照合USING_NLS_COMP_CIでは、照合BINARY_CIが使用されます。

• 疑似照合USING_NLS_COMP_AIでは、照合BINARY_AIが使用されます。

主キー列または一意キー列で使用される有効な照合がBINARYではない場合、この列に対して非表示の仮想列が作成されます。仮想列の式によって、元のキー列の文字値の照合キーが計算されます。主キー制約または一意制約は、元の列ではなく仮想列に内部的に作成されます。仮想列は、*_TAB_COLSファミリのデータ・ディクショナリ・ビューで参照できます。これらの非表示の仮想列のそれぞれについて、*_TAB_COLSビューのCOLLATED_COLUMN_IDに、対応する元のキー列を示す内部順序番号が含まれます。非表示の仮想列は、表の1000列の制限(MAX_COLUMNS初期化パラメータがSTANDARDに設定されている場合は1000列、MAX_COLUMNSがEXTENDEDに設定されている場合は4096列)までカウントされます。

関連項目:

• MAX_COLUMNS初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。

• 大/小文字を区別しない制約の例

• 照合の詳細は、『Oracle Databaseグローバリゼーション・サポート・ガイド』を参照してください。

制約状態の指定

制約を定義するとき、いつどのように制約を適用するかを指定できます。

constraint_state

constraint_stateは、表内指定と表外指定の両方に使用できます。DEFERRABLE句およびINITIALLY句は任意の順序で指定できますが、それを除く残りのコンポーネント句は、示されている順序で、各句を1回ずつだけ指定する必要があります。

DEFERRABLE句

DEFERRABLEおよびNOT DEFERRABLEパラメータは、後続のトランザクションで、SET CONSTRAINT(S)文を使用して、トランザクションが終了するまで制約のチェックを遅延できるようにするかどうかを指定します。この句を指定しない場合、NOT DEFERRABLEがデフォルトになります。

• NOT DEFERRABLEを指定すると、後続のトランザクションで、SET CONSTRAINT[S]句を使用して、トランザクションがコミットされるまでこの制約のチェックを遅延させることができません。NOT DEFERRABLE制約のチェックは、トランザクションの終わりまで遅延させることはできません。

  新しいNOT DEFERRABLE制約を宣言する場合、CREATE TABLEまたはALTER TABLE文のコミット時にその制約が有効である必要があります。有効でない場合、これらの文は正常に実行されません。

• DEFERRABLEを指定すると、後続のトランザクションで、SET CONSTRAINT[S]句を使用して、COMMIT文が発行されるまでこの制約のチェックを遅延させることができます。制約のチェックに失敗した場合、エラーが戻され、トランザクションはコミットされません。この設定によって、制約に違反する可能性のある変更をデータベースに行っている場合に、すべての変更が完了するまで、制約を一時的に使用禁止にすることが可能になります。

  ノート:

  オプティマイザは遅延可能制約の索引を使用可能として考慮しません。

制約の遅延可能状態は変更できません。これらのパラメータのいずれを指定するか、どちらも指定せずにNOT DEFERRABLE制約を暗黙的に有効にするかに関係なく、この句をALTER TABLE文に指定することはできません。制約を削除してから再作成する必要があります。

関連項目:

• トランザクションに対する制約のチェックの設定の詳細は、「SET CONSTRAINT[S]」を参照してください。

• 遅延制約の詳細は、『Oracle Database管理者ガイド』および『Oracle Database概要』を参照してください。

• 「DEFERRABLE制約の例」

[NOT] DEFERRABLEの制限事項

ビュー制約には、これらのパラメータのいずれも指定できません。

INITIALLY句

INITIALLY句は、DEFERRABLE句が指定されている制約に対するデフォルトのチェック動作を指定します。INITIALLY設定は、後続のトランザクションにSET CONSTRAINT(S)文を指定することで上書きできます。

• INITIALLY IMMEDIATEを指定すると、この制約は後続の各SQL文の終わりでチェックされます。INITIALLYを指定しない場合、INITIALLY IMMEDIATEがデフォルトになります。

  新しいINITIALLY IMMEDIATE制約を宣言する場合、CREATE TABLEまたはALTER TABLE文のコミット時にその制約が有効である必要があります。有効でない場合、これらの文は正常に実行されません。

• INITIALLY DEFERREDを指定すると、この制約は後続のトランザクションの終わりでチェックされます。

制約をNOT DEFERRABLEとして宣言した場合、この句は無効です。NOT DEFERRABLE制約は自動的にINITIALLY IMMEDIATEになり、INITIALLY DEFERREDに変更することはできないためです。

RELY句

RELYおよびNORELYパラメータは、クエリー・リライトでNOVALIDATEモードの制約を考慮するかどうかを指定します。RELYを指定すると、適用されていないクエリー・リライトに対するNOVALIDATEモードの制約は、アクティブになります。その制約はNOVALIDATEモードであるため、適用されません。デフォルト値はNORELYです。

適用されない制約は、通常、マテリアライズド・ビューおよびクエリー・リライトにのみ有効です。QUERY_REWRITE_INTEGRITYモードに従って、クエリー・リライトは、VALIDATEモードの制約、またはRELYパラメータが設定されたNOVALIDATEモードの制約を使用して、結合情報を確認します。

RELY句の制限事項

遅延不可能なNOT NULL制約をRELYに設定することはできません。

関連項目:

マテリアライズド・ビューおよびクエリー・リライトの詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。

索引による制約の適用

一意制約または主キー制約の状態を定義する場合は、制約の適用に使用する索引を指定すること、または制約の適用に使用する索引をOracleで自動作成することが可能です。

using_index_clause

using_index_clauseは、一意制約または主キー制約を有効にしている場合にのみ指定できます。using_index_clauseの句はどのような順序でも指定できますが、各句を指定できるのは1回のみです。

• schema.indexを指定すると、指定した索引を使用して制約が適用されます。索引がない、または制約の適用に索引が使用できない場合、エラーが戻されます。

• create_index_statementを指定すると、索引が作成され、制約の適用に使用されます。索引が作成できない、または制約の適用に索引が使用できない場合、エラーが戻されます。

• 既存の索引の指定も、新しい索引の作成も行わない場合は、索引が自動で作成されます。この場合は、次のようになります。

  • 索引には制約と同じ名前が割り当てられます。

  • 表がパーティション化されている場合、一意制約または主キー制約にローカル・パーティション索引またはグローバル・パーティション索引を指定できます。

using_index_clauseの制限事項

using_index_clauseには、次の制限事項が適用されます。

• この句は、ビュー制約に対して指定できません。

• この句は、NOT NULL、外部キーまたはCHECK制約には指定できません。

• 索引構成表の主キーが使用可能な場合は、索引の指定(schema.index)および作成(create_index_statement)はできません。

• index_attributesのparallel_clauseは指定できません。

• index_propertiesのINDEXTYPE IS ...句は、制約の定義では無効です。

関連項目:

• index_attributes、global_partitioned_index、local_partitioned_index句の詳細、および索引に関連するNOSORTとlogging_clauseの詳細は、「CREATE INDEX」を参照してください。

• 「physical_attributes_clause」と「PCTFREEパラメータ」および「storage_clause」を参照してください。

• 明示的な索引の制御例

ENABLE句

表のデータに制約を適用するには、ENABLEを指定します。

一意制約または主キー制約を使用可能にした場合、キーに索引が存在しないと、一意索引が作成されます。KEEP INDEXを指定しないかぎり、その後で制約が使用禁止になった場合にこの索引は削除されます。そのため、制約が使用可能になるたびに索引が再作成されます。

索引の再作成を避け、余分な索引を削除するには、最初に使用禁止にした主キー制約および一意制約を新しく作成します。その後、一意でない索引を作成して(または、既存の一意でない索引を使用して)制約を適用してください。制約が使用禁止の場合、一意でない索引は削除されず、後続のENABLE操作が容易になります。

• ENABLE VALIDATEを使用すると、すべての旧データと新データが制約に従うことを指定できます。制約が使用可能で、妥当性チェック済の場合は、すべてのデータが現在有効で、今後も有効であることが保証されます。

  整合性制約に違反する行が表にある場合、制約は使用禁止のままエラーを戻します。すべての行が制約に従っている場合、Oracleは制約を使用可能にします。新規データが整合性制約に違反する場合、その後の文は実行されず、整合性制約の違反を示すエラーが戻されます。

  主キー制約をENABLE VALIDATEモードに設定すると、妥当性チェック・プロセスによって主キー列にNULLが含まれないことが検証されます。これによるオーバーヘッドを回避するには、データを列に入力する前およびこの表の主キー制約を使用可能にする前に、主キーの各列にNOT NULLのマークを付けます。

• ENABLE NOVALIDATEを使用すると、制約データに対して新しく行うすべてのDML操作が制約に従うことが保証されます。表の既存データが制約に従っていることは保証されません。

VALIDATEもNOVALIDATEも指定しない場合、VALIDATEがデフォルトになります。

ENABLE NOVALIDATEからENABLE VALIDATEに単一制約状態を変更すると、パラレルで操作が実行できるため、読込み、書込みまたはその他のDDL操作が中断されません。

ENABLE句の制限事項

無効の一意キーまたは主キーを参照している外部キーを有効にすることはできません。

DISABLE句

整合性制約を無効にするには、DISABLEを指定します。データ・ディクショナリでは、使用禁止になっている整合性制約は、使用可能な制約とともに表示されます。この句を指定せずに制約を作成した場合は、その制約は自動的に使用可能になります。

• DISABLE VALIDATEは、制約を使用禁止にして制約の索引を削除しますが、制約は有効のままです。この機能を使用すると、大量のデータをロードでき、また索引用の領域が削減されるため、データ・ウェアハウスで非常に有効です。この設定を使用することで、ALTER TABLE文のexchange_partition_subpart句またはSQL*Loaderを使用して、データを非パーティション表からパーティション表にロードすることもできます。他のSQL文を使用した表に対するすべての変更(挿入、更新および削除)は禁止されます。

  関連項目:

  この設定の使用方法の詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。

• DISABLE NOVALIDATEは、Oracleによって制約がメンテナンスされないこと(使用禁止になっているため)、および制約が真であると保証されないこと(妥当性チェックが行われていないため)を示します。

  外部キー制約がDISABLE NOVALIDATE状態であっても、外部キーが参照している主キーを持つ表を削除できません。また、オプティマイザは、DISABLE NOVALIDATE状態でも制約を使用できます。

  関連項目:

  この設定を使用する状況の詳細は、『Oracle Database SQLチューニング・ガイド』を参照してください。

VALIDATEもNOVALIDATEも指定しない場合、NOVALIDATEがデフォルトになります。

一意索引を使用している一意制約または主キー制約を使用禁止にすると、一意索引は削除されます。その他のノートや制限事項は、「CREATE TABLE」の「enable_disable_clause」を参照してください。

VALIDATE | NOVALIDATE

VALIDATEおよびNOVALIDATEの動作は、制約が明示的にまたはデフォルトで有効/無効のどちらになっているかで異なります。VALIDATEおよびNOVALIDATEキーワードは、「ENABLE句」および「DISABLE句」で説明されています。

NOVALIDATEモードでの外部キー制約のノート

外部キー制約がNOVALIDATEモードの場合、表内の既存のデータが制約に準拠せず、QUERY_REWRITE_INTEGRITYパラメータがENFORCEDに設定されていないと、オプティマイザが表の問合せ時に結合の絞込みを使用することがあります。この場合、準拠しない外部キー値を持つ表の行をフィルタで除外する結合条件が問合せに含まれている場合でも、問合せはこのような行を返すことがあります。

制約の例外の処理

制約の状態を定義する場合は、制約に違反するすべての行のROWIDを格納する表を指定できます。

exceptions_clause

exceptions_clause構文を使用すると、例外の処理を定義できます。schemaを指定しない場合、自分のスキーマ内に例外表があるとみなされます。この句自体を指定しない場合、表の名前はEXCEPTIONSになります。EXCEPTIONS表または指定した表は、ローカル・データベースに存在する必要があります。

次のいずれかのスクリプトを使用して、EXCEPTIONS表を作成できます。

• UTLEXCPT.SQLは、物理ROWIDを使用します。そのため、行は、索引構成表からではなく従来表から収集されます。(次の注意を参照。)

• UTLEXPT1.SQLは、ユニバーサルROWIDを使用します。そのため、行は、従来表と索引構成表の両方から収集されます。

独自の例外表を作成する場合、これら2つのスクリプトのいずれかで規定される形式に従う必要があります。

ユニバーサルROWIDではなく、主キーに基づく索引構成表から例外を収集する場合、索引構成表ごとに別の例外表を作成し、主キー記憶域を確保する必要があります。スクリプトを変更および再発行することによって、別の名前の例外表を複数作成できます。

exceptions_clauseの制限事項

exceptions_clauseには、次の制限事項が適用されます。

• この句は、ビュー制約に対して指定できません。

• この文が正常に終了されるまでROWIDは存在しないため、この句をCREATE TABLE文に指定することはできません。

  関連項目:

  • SQLスクリプトの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』のDBMS_IOTパッケージを参照してください。

  • 移行行および連鎖行の削除については、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。

ビュー制約

Oracleでは、ビュー制約を適用していません。ただし、ビューに対する操作には、基となる実表に定義されている整合性制約が適用されます。つまり、実表に対する制約によって、ビューに制約を適用できます。

ビュー制約のノート

ビュー制約は表制約のサブセットであり、次の制限事項があります。

• ビューには、一意制約、主キー制約および外部キー制約のみ指定できます。ただし、WITH CHECK OPTION句を使用してビューを定義することは可能です。これは、ビューにCHECK制約を指定することと同じです。

• ビュー制約は、DISABLE NOVALIDATEモードのみでサポートされています。他のモードは指定できません。ビュー制約を宣言する場合は、キーワードDISABLEを指定する必要があります。NOVALIDATEはデフォルトのため、明示的に指定する必要はありません。

• RELYおよびNORELYパラメータはオプションです。ビュー制約は適用されないため、通常はRELYパラメータで指定し、より有効に使用します。RELYまたはNORELYキーワードは、DISABLEキーワードより前に指定する必要があります。

• ビュー制約は直接適用されないため、INITIALLY DEFERREDまたはDEFERRABLEは指定できません。

• using_index_clause、exceptions_clause句、またはreferences_clauseのON DELETE句は指定できません。

• オブジェクト列の属性にビュー制約を定義することはできません。

外部表の制約

Oracle Database 12cリリース2 (12.2)以降、外部表には、NOT NULL、一意制約、主キー制約および外部キー制約を指定できます。

外部表に対するNOT NULL制約が適用され、列にNULLが含まれないようにします。

一意制約、主キー制約および外部キーの制約は、RELY DISABLEモードでのみ外部表に対してサポートされます。これらの制約を作成する場合は、キーワードRELYおよびDISABLEを指定する必要があります。これらの制約は宣言的であり、適用されません。より多くのオプティマイザ変換を考慮できるため、問合せパフォーマンスが向上し、リソース使用量が削減される可能性があります。オプティマイザがこれらのRELY DISABLE制約を利用できるようにするには、QUERY_REWRITE_INTEGRITY初期化パラメータをtrustedまたはstale_toleratedに設定する必要があります。

例

一意キーの例

次の文は、サンプル表sh.promotionsを作成する文の例です。この文は制約を表内に定義し、promo_id列で一意キーを暗黙的に使用可能にします(この例では、他の制約は省略されています)。

CREATE TABLE promotions_var1
    ( promo_id         NUMBER(6)
                       CONSTRAINT promo_id_u  UNIQUE
    , promo_name       VARCHAR2(20)
    , promo_category   VARCHAR2(15)
    , promo_cost       NUMBER(10,2)
    , promo_begin_date DATE
    , promo_end_date   DATE
    ) ;

制約promo_id_uは、一意キーとしてpromo_id列を識別します。この制約によって、表に同じIDを持つ複数の販売促進の行が存在しないことが保証されます。ただし、識別子のない行は許可されます。

この制約を表外に定義して使用可能にすることもできます。

CREATE TABLE promotions_var2
    ( promo_id         NUMBER(6)
    , promo_name       VARCHAR2(20)
    , promo_category   VARCHAR2(15)
    , promo_cost       NUMBER(10,2)
    , promo_begin_date DATE
    , promo_end_date   DATE
    , CONSTRAINT promo_id_u UNIQUE (promo_id)
   USING INDEX PCTFREE 20
      TABLESPACE stocks
      STORAGE (INITIAL 8M) ); 

この文にはusing_index_clauseも含まれています。この句は、制約を使用可能にするために作成される索引の記憶特性を指定します。

複合一意キーの例

次の文は、oe.warehouses表のwarehouse_id列とwarehouse_name列を組み合せて複合一意キーを定義し、有効にします。

ALTER TABLE warehouses
   ADD CONSTRAINT wh_unq UNIQUE (warehouse_id, warehouse_name)
   USING INDEX PCTFREE 5
   EXCEPTIONS INTO wrong_id;

wh_unq制約によって、warehouse_idとwarehouse_nameを組み合せた値が表の中に複数存在しないことが保証されます。

このADD CONSTRAINT句には、制約以外のプロパティも指定できます。

• USING INDEX句は、制約を使用可能にするために作成される索引の記憶特性を指定します。

• EXCEPTIONS INTO句によって、制約に違反する行がwarehouses表に含まれている場合、その行に関する情報がwrong_id表に書き込まれます。wrong_id例外表が存在しない場合、この文は正常に実行されません。

主キーの例

次の文は、サンプル表hr.locationsを作成する文の例です。locations_demo表を作成し、location_id列に主キーを定義して使用可能にします(この例では、hr.locations表の他の制約は省略されています)。

CREATE TABLE locations_demo
    ( location_id    NUMBER(4) CONSTRAINT loc_id_pk PRIMARY KEY
    , street_address VARCHAR2(40)
    , postal_code    VARCHAR2(12)
    , city           VARCHAR2(30)
    , state_province VARCHAR2(25)
    , country_id     CHAR(2)
    ) ;

表内に指定されているloc_id_pk制約は、location_id列をlocations_demo表の主キーとして識別します。この制約によって、表の中の複数の所在地が同一の所在地識別子を持つことはなく、かつ所在地識別子がNULLにならないことが保証されます。

この制約を表外に定義して使用可能にすることもできます。

CREATE TABLE locations_demo
    ( location_id    NUMBER(4) 
    , street_address VARCHAR2(40)
    , postal_code    VARCHAR2(12)
    , city           VARCHAR2(30)
    , state_province VARCHAR2(25)
    , country_id     CHAR(2)
    , CONSTRAINT loc_id_pk PRIMARY KEY (location_id));

NOT NULLの例

次の文は、(「主キーの例」で作成した) locations_demo表を変更し、country_id列にNOT NULL制約を定義して有効にします。

ALTER TABLE locations_demo
   MODIFY (country_id CONSTRAINT country_nn NOT NULL); 

制約country_nnによって、country_idがNULLの所在地の行が表にないことが保証されます。

複合主キーの例

次の文は、sh.salesサンプル表のprod_id列およびcust_id列を組み合せて複合主キーを定義します。

ALTER TABLE sales 
    ADD CONSTRAINT sales_pk PRIMARY KEY (prod_id, cust_id) DISABLE; 

この制約は、sales表の主キーとしてprod_id列とcust_id列の組合せを識別します。この制約によって、表の中の複数の行がprod_id列とcust_id列に同じ組合せの値を持たないことが保証されます。

この制約句(PRIMARY KEY)では、次の制約のプロパティも指定しています。

• 制約定義によって制約名が指定されていないため、この制約に対する名前がOracleによって自動的に生成されます。

• DISABLE句によって制約が定義されますが、使用可能にはなりません。

外部キー制約の例

次の文は、dept_20表を作成し、departments表のdepartment_id列の主キーを参照するdepartment_id列に、外部キーを定義して有効にします。

CREATE TABLE dept_20 
   (employee_id     NUMBER(4), 
    last_name       VARCHAR2(10), 
    job_id          VARCHAR2(9), 
    manager_id      NUMBER(4), 
    hire_date       DATE, 
    salary          NUMBER(7,2), 
    commission_pct  NUMBER(7,2), 
    department_id   CONSTRAINT fk_deptno 
                    REFERENCES departments(department_id) ); 

fk_deptno制約によって、dept_20表の従業員が属しているすべての部門がdepartments表に含まれることが保証されます。ただし、部門番号がNULL値の従業員(どの部門にも属さない従業員)がいてもかまいません。すべての従業員がいずれかの部門に割り当てられるようにするには、dept_20表のdepartment_id列に対して、REFERENCES制約の他にNOT NULL制約を作成します。

この制約を定義して使用可能にする前に、departments表のdepartment_id列を主キーまたは一意キーとして指定する制約を定義して使用可能にする必要があります。

制約が表内に定義されているため、この外部キー制約定義はFOREIGN KEY句を使用しません。また、この列には参照キーのデータ型が自動的に割り当てられるため、department_id列のデータ型は必要ありません。

制約定義によって親表と参照キーの列の両方が指定されます。参照キーは親表の主キーであるため、参照キーの列名の指定は任意です。

この外部キー制約を表外に定義することもできます。

CREATE TABLE dept_20 
   (employee_id     NUMBER(4), 
    last_name       VARCHAR2(10), 
    job_id          VARCHAR2(9), 
    manager_id      NUMBER(4), 
    hire_date       DATE, 
    salary          NUMBER(7,2), 
    commission_pct  NUMBER(7,2), 
    department_id, 
   CONSTRAINT fk_deptno 
      FOREIGN  KEY (department_id) 
      REFERENCES  departments(department_id) ); 

これらの外部キー定義は、両方ともON DELETE句を指定していないため、従業員がいる部門は削除できません。

ON DELETEの例

次の文は、dept_20表を作成し、2つの参照整合性制約を定義して有効にした後、ON DELETE句を使用します。

CREATE TABLE dept_20 
   (employee_id     NUMBER(4) PRIMARY KEY, 
    last_name       VARCHAR2(10), 
    job_id          VARCHAR2(9), 
    manager_id      NUMBER(4) CONSTRAINT fk_mgr
                    REFERENCES employees ON DELETE SET NULL, 
    hire_date       DATE, 
    salary          NUMBER(7,2), 
    commission_pct  NUMBER(7,2), 
    department_id   NUMBER(2)   CONSTRAINT fk_deptno 
                    REFERENCES departments(department_id) 
                    ON DELETE CASCADE ); 

最初のON DELETE句によって、従業員番号2332の上司がemployees表から削除された場合、2332の上司の部下だったdept_20表のすべての従業員のmanager_id値がNULLになります。

次のON DELETE句によって、departments表のdepartment_id値が削除されると、これに依存するdept_20表の行のdepartment_id値も同時に削除されます。たとえば、部門番号20がdepartments表から削除されると、部門番号20の全従業員がdept_20表から削除されます。

複合外部キー制約の例

次の文は、dept_20表のemployee_id列およびhire_date列を組み合せて外部キーを定義し、有効にします。

ALTER TABLE dept_20
   ADD CONSTRAINT fk_empid_hiredate
   FOREIGN KEY (employee_id, hire_date)
   REFERENCES hr.job_history(employee_id, start_date)
   EXCEPTIONS INTO wrong_emp;

fk_empid_hiredate制約によって、dept_20表の中すべての従業員が、employees表に存在するemployee_idとhire_dateの組合せを持つことが保証されます。この制約を定義して使用可能にする前に、employees表のemployee_id列とhire_date列の組合せを主キーまたは一意キーとして指定する制約を定義して使用可能にする必要があります。

EXCEPTIONS INTO句によって、制約に違反する行がdept_20表に含まれている場合、その行に関する情報がwrong_emp表に書き込まれます。wrong_emp例外表が存在しない場合、この文は正常に実行されません。

CHECK制約の例

次の文ではdivisions表を作成し、表の各列のcheck制約を指定します。

CREATE TABLE divisions  
   (div_no    NUMBER  CONSTRAINT check_divno
              CHECK (div_no BETWEEN 10 AND 99) 
              DISABLE, 
    div_name  VARCHAR2(9)  CONSTRAINT check_divname
              CHECK (div_name = UPPER(div_name)) 
              DISABLE, 
    office    VARCHAR2(10)  CONSTRAINT check_office
              CHECK (office IN ('DALLAS','BOSTON',
              'PARIS','TOKYO')) 
              DISABLE); 

列に定義されている各制約によって、列の値が次のように制限されます。

• check_divnoによって、部門番号は必ず10から99の範囲内になります。

• check_divnameによって、部門名はすべて大文字になります。

• check_officeによって、事務所の所在地はDallas、Boston、ParisまたはTokyoのいずれかに制限されます。

それぞれのCONSTRAINT句にDISABLE句が指定されているため、これらの制約は定義されるのみで、使用可能にはされません。

次の文は、dept_20表を作成し、CHECK制約を表外に定義して暗黙的に使用可能にします。

CREATE TABLE dept_20
   (employee_id     NUMBER(4) PRIMARY KEY, 
    last_name       VARCHAR2(10), 
    job_id          VARCHAR2(9), 
    manager_id      NUMBER(4), 
    salary          NUMBER(7,2), 
    commission_pct  NUMBER(7,2), 
    department_id   NUMBER(2),
    CONSTRAINT check_sal CHECK (salary * commission_pct <= 5000));

この制約は、不等式の条件を使用して、従業員の歩合総額(salaryとcommission_pctを掛けた金額)を$5000に制限します。

• 従業員の給与と歩合がNULL以外の値の場合、制約を満たすには、この金額が$5000を超えてはいけません。

• 従業員の給与または歩合がNULL値の場合、条件の結果は不明となり、その従業員は自動的に制約を満たします。

この例の制約句には制約名が指定されていないため、制約の名前が自動的に生成されます。

次の文は、1つの主キー制約、2つの外部キー制約、1つのNOT NULL制約および2つのCHECK制約を定義して使用可能にします。

CREATE TABLE order_detail 
  (CONSTRAINT pk_od PRIMARY KEY (order_id, part_no), 
   order_id    NUMBER 
      CONSTRAINT fk_oid 
         REFERENCES oe.orders(order_id), 
   part_no     NUMBER 
      CONSTRAINT fk_pno 
         REFERENCES oe.product_information(product_id), 
   quantity    NUMBER 
      CONSTRAINT nn_qty NOT NULL 
      CONSTRAINT check_qty CHECK (quantity > 0), 
   cost        NUMBER 
      CONSTRAINT check_cost CHECK (cost > 0) ); 

この制約によって、表のデータに対して次の規則を適用できます。

• pk_odは、order_id列とpart_no列の組合せを表の主キーとして指定します。この制約を満たすためには、order_id列およびpart_no列の組合せが同じである行が、表内に2つあってはいけません。また、order_id列およびpart_no列では、行にNULLを入れることはできません。

• fk_oidは、サンプル・スキーマoe内のorders表にあるorder_id列を参照する外部キーとして、order_id列を指定します。order_detail.order_id列に追加されるすべての新しい値は、oe.orders.order_id列にあらかじめ存在する必要があります。

• fk_pnoは、oeが所有するproduct_information表にあるproduct_id列を参照する外部キーとして、product_id列を指定します。order_detail.product_id列に追加されるすべての新しい値は、oe.product_information.product_id列にあらかじめ存在する必要があります。

• nn_qtyは、quantity列に対してNULLを禁止します。

• check_qtyによって、quantity列の値は必ず0(ゼロ)より大きくなります。

• check_costによって、cost列の値は必ず0(ゼロ)より大きくなります。

この例は、制約句と列定義について、次の点についても示しています。

• 表外制約定義は、列定義の前と後のどちらにも指定できます。この例では、pk_od制約の表外定義が、列定義の前にあります。

• 列定義には、複数の表内制約定義を含めることができます。この例では、quantity列の定義はnn_qty制約とcheck_qty制約の両方の定義を含んでいます。

• 表には、複数のCHECK制約を指定できます。複数のビジネス・ルールを適用する複雑な条件を持つ1つのCHECK制約よりも、それぞれ1つのビジネス・ルールのみを適用する単純な条件を持つ複数のCHECK制約を使用してください。矛盾している制約があると、その制約を識別するエラー・メッセージが戻されます。エラーが検出された制約が1つのビジネス・ルールのみを有効にする場合、このようなエラー・メッセージの方が、矛盾のあるビジネス・ルールを正確に識別できます。

PRECHECKを使用した表の作成: 例

次の例では、PRECHECK制約を列Price、Color、Description、定数NUMBER、制約TC1に対して持つ表Productを作成します。

CREATE TABLE Product(
  Id NUMBER NOT NULL PRIMARY KEY,
  Name VARCHAR2(50),
  Price NUMBER CHECK (mod(price,4) = 0 and 10 <> price) PRECHECK,
  Color NUMBER CHECK (Color >= 10 and Color <=50 and mod(color,2) = 0)
    PRECHECK,
  Description VARCHAR2(50) CHECK (Length(Description) <= 40) PRECHECK,
  Constant NUMBER CHECK (Constant=10) PRECHECK,
  CONSTRAINT TC1 CHECK (Color > 0 AND Price > 10) PRECHECK,
  CONSTRAINT TC2 CHECK (CATEGORY IN ('Home', 'Apparel') AND Price > 10)
);
Table PRODUCT created.

ALTER TABLEを使用して、新しい制約に事前チェック状態を追加します。

ALTER TABLE Product MODIFY (Name VARCHAR2(50) CHECK 
  (regexp_like(Name, '^Product')) PRECHECK);

既存のコスト制約状態への事前チェックの追加:

ALTER TABLE Product MODIFY CONSTRAINT TC2 PRECHECK;

既存の事前チェック状態の削除:

ALTER TABLE Product MODIFY CONSTRAINT TC1 NOPRECHECK;

USER_CONSTRAINTSのPRECHECK状態の確認: 例

次の表Productがあるとします。

SQL> CREATE TABLE Product(
       Id NUMBER NOT NULL PRIMARY KEY,
       Name VARCHAR2(50),
       Category VARCHAR2(10) NOT NULL,
       Price NUMBER CHECK (mod(price,4) = 0 and 10 <> price),
       Color NUMBER CHECK (Color >= 10 and Color <=50) PRECHECK,
       Description VARCHAR2(50) CHECK (Length(Description) <= 40),
       Created_At DATE,
       Updated_At DATE,
       CONSTRAINT TC1 CHECK (Color > 0 AND Price > 10),
       CONSTRAINT TC2 CHECK (CATEGORY IN ('Home', 'Apparel')) NOPRECHECK,
       CONSTRAINT TC3 CHECK (Created_At > Updated_At)
     );

Table PRODUCT created.

PRECHECK状態は、USER_CONSTRAINTSで次のように確認できます。

SELECT CONSTRAINT_NAME, SEARCH_CONDITION, PRECHECK 
  FROM USER_CONSTRAINTS 
  WHERE table_name='PRODUCT' and constraint_type='C';

結果は次のとおりです。

CONSTRAINT_NAME                          SEARCH_CONDITION                    PRECHECK
__________________                       ___________________________________ _____________
SYS_C008676 "ID"                         IS NOT NULL
SYS_C008677 "CATEGORY"                   IS NOT NULL
SYS_C008678  mod(price,4)                = 0 and 10 <> price                  PRECHECK
SYS_C008679 Color                        >= 10 and Color <=50                 PRECHECK
SYS_C008680 Length(Description)          <= 40                                PRECHECK
TC1                                      Color > 0 AND Price > 10             PRECHECK
TC2                                      CATEGORY IN ('Home', 'Apparel')      NOPRECHECK
TC3                                      Created_At > Updated_At              NOPRECHECK

8 rows selected.

インライン制約とアウトライン制約の両方で、複数の制約が自動的に値に設定されます。

大/小文字を区別しない制約の例

次の文は、親子関係の2つの表を作成します。親表は製品説明の表、子表は製品コンポーネント説明の表です。製品と説明の値が明確になるように、一意制約が定義されています。説明のため、製品とコンポーネントIDは大/小文字を区別しない文字値になっています。(現実世界のアプリケーションでは、通常、主キーIDは数値であるか、大/小文字が正規化されています。)

CREATE TABLE products
    ( product_id VARCHAR2(20) COLLATE BINARY_CI
           CONSTRAINT product_pk PRIMARY KEY
    , description VARCHAR2(1000) COLLATE BINARY_CI
           CONSTRAINT product_description_unq UNIQUE
    );

CREATE TABLE product_components
    ( component_id VARCHAR2(40) COLLATE BINARY_CI
           CONSTRAINT product_component_pk PRIMARY KEY
    , product_id CONSTRAINT product_component_fk REFERENCES products(product_id)
    , description VARCHAR2(1000) COLLATE BINARY_CI
           CONSTRAINT product_component_descr_unq UNIQUE
    );

外部キー列のデータ型または照合を指定しない場合は、親キー列から継承されます。

次の文は、製品とそのコンポーネントを表に追加します。

INSERT INTO products(product_id, description)
            VALUES('BICY0001', 'Men''s bicycle, fr 21", wh 24", gear 3x7');
INSERT INTO product_components(component_id, product_id, description)
            VALUES('BICY0001_FRAME01', 'BICY0001', 'Aluminium frame 21"');
INSERT INTO product_components(component_id, product_id, description)
            VALUES('BICY0001_WHEEL01', 'bicy0001', 'Wheels 24"');
INSERT INTO product_components(component_id, product_id, description)
            VALUES('BICY0001_GEAR01', 'Bicy0001', 'Front derailleur 3 chainrings');
INSERT INTO product_components(component_id, product_id, description)
            VALUES('BICY0001_gear02', 'BiCy0001', 'Rear derailleur 7 chainrings');

製品IDの大/小文字がコンポーネント行ごとに異なることに注意してください。製品IDの主キーは大/小文字を区別しないものとして宣言されているため、同じIDについて使用可能なすべての大/小文字の組合せは等しいものとみなされます。

次の文は、大/小文字のみ異なる同じ説明では別の製品を入力できないことを示しています。これは、エラーORA-00001: unique constraint (schema.PRODUCT_DESCRIPTION_UNQ) violatedで失敗します。

INSERT INTO products(product_id, description)
            VALUES('BICY0002', 'MEN''S BICYCLE, fr 21", wh 24", gear 3x7');

同様に、次の文は、製品表の主キー制約が大/小文字を区別せず、大/小文字のみ異なる値は使用できないことを示しています。これは、エラーORA-00001: unique constraint (schema.PRODUCT_PK) violatedで失敗します。

INSERT INTO products(component_id, product_id, description)
            VALUES('bicy0001', 'Women''s bicycle, fr 21", wh 24", gear 2x6');

次の文は、大/小文字のみ異なる同じ説明では別のコンポーネントを入力できないことを示しています。これは、エラーORA-00001: unique constraint (schema.PRODUCT_COMPONENT_DESCR_UNQ) violatedで失敗します。

INSERT INTO product_components(component_id, product_id, description)
            VALUES('BICY0001_gear03', 'BiCy0001', 'REAR DERAILLEUR 7 CHAINRINGS');

属性レベル制約の例

次の文は、students表のname列のfirst_name属性とlast_name属性の両方に対して値が存在することを保証します。

CREATE TYPE person_name AS OBJECT
   (first_name VARCHAR2(30), last_name VARCHAR2(30));
/

CREATE TABLE students (name person_name, age INTEGER,
   CHECK (name.first_name IS NOT NULL AND 
          name.last_name IS NOT NULL));

REF制約の例

次の例は、サンプル・スキーマのオブジェクト型であるcust_address_typの複製を作成し、SCOPE制約が指定されたREF列を含む表を作成します。

CREATE TYPE cust_address_typ_new AS OBJECT
    ( street_address     VARCHAR2(40)
    , postal_code        VARCHAR2(10)
    , city               VARCHAR2(30)
    , state_province     VARCHAR2(10)
    , country_id         CHAR(2)
    );
/
CREATE TABLE address_table OF cust_address_typ_new;

CREATE TABLE customer_addresses (
   add_id NUMBER, 
   address REF cust_address_typ_new
   SCOPE IS address_table);

次の例は、同じ表を作成しますが、親表のオブジェクト識別子列を参照するREF列に参照整合性制約が指定されています。

CREATE TABLE customer_addresses (
   add_id NUMBER,
   address REF cust_address_typ REFERENCES address_table);

次の例では、department_typ型およびdepartments_obj_t表(「オブジェクト表の作成: 例」で作成)を使用します。この文を実行すると、有効範囲付きREFを持つ表が作成されます。

CREATE TABLE employees_obj
   ( e_name   VARCHAR2(100),
     e_number NUMBER,
     e_dept   REF department_typ SCOPE IS departments_obj_t );

次の文は、参照整合性制約が定義されているREF列を含む表を作成します。

CREATE TABLE employees_obj
   ( e_name   VARCHAR2(100),
     e_number NUMBER,
     e_dept   REF department_typ REFERENCES departments_obj_t);

明示的な索引の制御例

次の文は、Oracleが制約を適用する場合に使用する索引を、明示的に制御する一意制約または主キー制約を作成する別の方法を示します。

CREATE TABLE promotions_var3
    ( promo_id         NUMBER(6)
    , promo_name       VARCHAR2(20)
    , promo_category   VARCHAR2(15)
    , promo_cost       NUMBER(10,2)
    , promo_begin_date DATE
    , promo_end_date   DATE
    , CONSTRAINT promo_id_u UNIQUE (promo_id, promo_cost)
         USING INDEX (CREATE UNIQUE INDEX promo_ix1
            ON promotions_var3 (promo_id, promo_cost))
    , CONSTRAINT promo_id_u2 UNIQUE (promo_cost, promo_id) 
         USING INDEX promo_ix1);

この例は、1つの制約に対して1つの索引を作成し、その索引を使用して同じ文に別の制約を作成して使用可能にできることも示しています。

DEFERRABLE制約の例

次の文は、scores列にCHECK制約NOT DEFERRABLE INITIALLY IMMEDIATE(デフォルト)を使用したgames表を作成します。

CREATE TABLE games (scores NUMBER CHECK (scores >= 0));

次の文は、列に対する一意制約をINITIALLY DEFERRED DEFERRABLEとして定義します。

CREATE TABLE games
  (scores NUMBER, CONSTRAINT unq_num UNIQUE (scores)
   INITIALLY DEFERRED DEFERRABLE);