Skip Headers
Oracle® Database SQL Language Reference
11g Release 2 (11.2)
Part Number E10592-02
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Master Index
Master Index		 Go to Feedback page
Contact Us
Go to previous page
Previous		 Go to next page
Next
View PDF

constraint

Purpose

Use a constraint to define an integrity constraint—a rule that restricts the values in a database. Oracle Database lets you create six types of constraints and lets you declare them in two ways.

The six types of integrity constraint are described briefly here and more fully in "Semantics":

You can define constraints syntactically in two ways:

NOT NULL constraints must be declared inline. All other constraints can be declared either inline or out of line.

Constraint clauses can appear in the following statements:

View Constraints Oracle Database does not enforce view constraints. However, you can enforce constraints on views through constraints on base tables.

You can specify only unique, primary key, and foreign key constraints on views, and they are supported only in DISABLE NOVALIDATE mode. You cannot define view constraints on attributes of an object column.

See Also:

"View Constraints" for additional information on view constraints and "DISABLE Clause" for information on DISABLE NOVALIDATE mode

Prerequisites

You must have the privileges necessary to issue the statement in which you are defining the constraint.

To create a foreign key constraint, in addition, the parent table or view must be in your own schema or you must have the REFERENCES privilege on the columns of the referenced key in the parent table or view.

Syntax

constraint::=

Description of constraint.gif follows
Description of the illustration constraint.gif

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

inline_constraint::=

Description of inline_constraint.gif follows
Description of the illustration inline_constraint.gif

(references_clause::=)

out_of_line_constraint::=

Description of out_of_line_constraint.gif follows
Description of the illustration out_of_line_constraint.gif

(references_clause::=, constraint_state::=)

inline_ref_constraint::=

Description of inline_ref_constraint.gif follows
Description of the illustration inline_ref_constraint.gif

(references_clause::=, constraint_state::=)

out_of_line_ref_constraint::=

Description of out_of_line_ref_constraint.gif follows
Description of the illustration out_of_line_ref_constraint.gif

(references_clause::=, constraint_state::=)

references_clause::=

Description of references_clause.gif follows
Description of the illustration references_clause.gif

constraint_state::=

Description of constraint_state.gif follows
Description of the illustration constraint_state.gif

(using_index_clause::=, exceptions_clause::=)

using_index_clause::=

Description of using_index_clause.gif follows
Description of the illustration using_index_clause.gif

(create_index::=, index_properties::=)

index_properties::=

Description of index_properties.gif follows
Description of the illustration index_properties.gif

(global_partitioned_index::=, local_partitioned_index ::=--part of CREATE INDEX, index_attributes::=. The INDEXTYPE IS ... clause is not valid when defining a constraint.)

index_attributes::=

Description of index_attributes.gif follows
Description of the illustration index_attributes.gif

(physical_attributes_clause::=, logging_clause::=, key_compression::=--all part of CREATE INDEX, parallel_clause: not supported in using_index_clause)

exceptions_clause::=

Description of exceptions_clause.gif follows
Description of the illustration exceptions_clause.gif

Semantics

This section describes the semantics of constraint. For additional information, refer to the SQL statement in which you define or redefine a constraint for a table or view.

Oracle Database does not support constraints on columns or attributes whose type is a user-defined object, nested table, VARRAY, REF, or LOB, with two exceptions:

CONSTRAINT constraint_name  Specify a name for the constraint. If you omit this identifier, then Oracle Database generates a name with the form SYS_Cn. Oracle stores the name and the definition of the integrity constraint in the USER_, ALL_, and DBA_CONSTRAINTS data dictionary views (in the CONSTRAINT_NAME and SEARCH_CONDITION columns, respectively).

See Also:

Oracle Database Reference for information on the data dictionary views

NOT NULL Constraints

A NOT NULL constraint prohibits a column from containing nulls. The NULL keyword by itself does not actually define an integrity constraint, but you can specify it to explicitly permit a column to contain nulls. You must define NOT NULL and NULL using inline specification. If you specify neither NOT NULL nor NULL, then the default is NULL.

NOT NULL constraints are the only constraints you can specify inline on XMLType and VARRAY columns.

To satisfy a NOT NULL constraint, every row in the table must contain a value for the column.

Note:

Oracle Database does not index table rows in which all key columns are null except in the case of bitmap indexes. Therefore, if you want an index on all rows of a table, then you must either specify NOT NULL constraints for at least one of the index key columns or create a bitmap index.

Restrictions on NOT NULL Constraints NOT NULL constraints are subject to the following restrictions:

See Also:

"Attribute-Level Constraints Example" and "NOT NULL Example"

Unique Constraints

A unique constraint designates a column as a unique key. A composite unique key designates a combination of columns as the unique key. When you define a unique constraint inline, you need only the UNIQUE keyword. When you define a unique constraint out of line, you must also specify one or more columns. You must define a composite unique key out of line.

To satisfy a unique constraint, no two rows in the table can have the same value for the unique key. However, the unique key made up of a single column can contain nulls. To satisfy a composite unique key, no two rows in the table or view can have the same combination of values in the key columns. Any row that contains nulls in all key columns automatically satisfies the constraint. However, two rows that contain nulls for one or more key columns and the same combination of values for the other key columns violate the constraint.

When you specify a unique constraint on one or more columns, Oracle implicitly creates an index on the unique key. If you are defining uniqueness for purposes of query performance, then Oracle recommends that you instead create the unique index explicitly using a CREATE UNIQUE INDEX statement. You can also use the CREATE UNIQUE INDEX statement to create a unique function-based index that defines a conditional unique constraint. See "Using a Function-based Index to Define Conditional Uniqueness: Example" for more information.

Restrictions on Unique Constraints Unique constraints are subject to the following restrictions:

See Also:

"Unique Key Example" and Composite Unique Key Example

Primary Key Constraints

A primary key constraint designates a column as the primary key of a table or view. A composite primary key designates a combination of columns as the primary key. When you define a primary key constraint inline, you need only the PRIMARY KEY keywords. When you define a primary key constraint out of line, you must also specify one or more columns. You must define a composite primary key out of line.

To satisfy a primary key constraint:

When you create a primary key constraint:

When you drop a primary key constraint:

Restrictions on Primary Key Constraints Primary constraints are subject to the following restrictions:

See Also:

"Primary Key Example" and "Composite Primary Key Example"

Foreign Key Constraints

A foreign key constraint (also called a referential integrity constraint) designates a column as the foreign key and establishes a relationship between that foreign key and a specified primary or unique key, called the referenced key. A composite foreign key designates a combination of columns as the foreign key.

The table or view containing the foreign key is called the child object, and the table or view containing the referenced key is called the parent object. The foreign key and the referenced key can be in the same table or view. In this case, the parent and child tables are the same. If you identify only the parent table or view and omit the column name, then the foreign key automatically references the primary key of the parent table or view. The corresponding column or columns of the foreign key and the referenced key must match in order and data type.

You can define a foreign key constraint on a single key column either inline or out of line. You must specify a composite foreign key and a foreign key on an attribute out of line.

To satisfy a composite foreign key constraint, the composite foreign key must refer to a composite unique key or a composite primary key in the parent table or view, or the value of at least one of the columns of the foreign key must be null.

You can designate the same column or combination of columns as both a foreign key and a primary or unique key. You can also designate the same column or combination of columns as both a foreign key and a cluster key.

You can define multiple foreign keys in a table or view. Also, a single column can be part of more than one foreign key.

Restrictions on Foreign Key Constraints Foreign key constraints are subject to the following restrictions:

See Also:

references_clause Foreign key constraints use the references_clause syntax. When you specify a foreign key constraint inline, you need only the references_clause. When you specify a foreign key constraint out of line, you must also specify the FOREIGN KEY keywords and one or more columns.

ON DELETE Clause The ON DELETE clause lets you determine how Oracle Database automatically maintains referential integrity if you remove a referenced primary or unique key value. If you omit this clause, then Oracle does not allow you to delete referenced key values in the parent table that have dependent rows in the child table.

Restriction on ON DELETE You cannot specify this clause for a view constraint.

See Also:

"ON DELETE Example"

Check Constraints

A check constraint lets you specify a condition that each row in the table must satisfy. To satisfy the constraint, each row in the table must make the condition either TRUE or unknown (due to a null). When Oracle evaluates a check constraint condition for a particular row, any column names in the condition refer to the column values in that row.

The syntax for inline and out-of-line specification of check constraints is the same. However, inline specification can refer only to the column (or the attributes of the column if it is an object column) currently being defined, whereas out-of-line specification can refer to multiple columns or attributes.

Oracle does not verify that conditions of check constraints are not mutually exclusive. Therefore, if you create multiple check constraints for a column, design them carefully so their purposes do not conflict. Do not assume any particular order of evaluation of the conditions.

See Also:

Restrictions on Check Constraints Check constraints are subject to the following restrictions:

REF Constraints

REF constraints let you describe the relationship between a column of type REF and the object it references.

ref_constraint REF constraints use the ref_constraint syntax. You define a REF constraint either inline or out of line. Out-of-line specification requires you to specify the REF column or attribute you are further describing.

Both inline and out-of-line specification let you define a scope constraint, a rowid constraint, or a referential integrity constraint on a REF column.

If the scope table or referenced table of the REF column has a primary-key-based object identifier, then the REF column is a user-defined REF column.

See Also:

SCOPE REF Constraints

In a table with a REF column, each REF value in the column can conceivably reference a row in a different object table. The SCOPE clause restricts the scope of references to a single table, scope_table. The values in the REF column or attribute point to objects in scope_table, in which object instances of the same type as the REF column are stored.

Specify the SCOPE clause to restrict the scope of references in the REF column to a single table. For you to specify this clause, scope_table must be in your own schema or you must have SELECT privileges on scope_table or SELECT ANY TABLE system privileges. You can specify only one scope table for each REF column.

Restrictions on Scope Constraints Scope constraints are subject to the following restrictions:

Rowid REF Constraints

Specify WITH ROWID to store the rowid along with the REF value in ref_column or ref_attribute. Storing the rowid with the REF value can improve the performance of dereferencing operations, but will also use more space. Default storage of REF values is without rowids.

See Also:

The function DEREF for an example of dereferencing

Restrictions on Rowid Constraints Rowid constraints are subject to the following restrictions:

Referential Integrity Constraints on REF Columns

The references_clause of the ref_constraint syntax lets you define a foreign key constraint on the REF column. This clause also implicitly restricts the scope of the REF column or attribute to the referenced table. However, whereas a foreign key constraint on a non-REF column references an actual column in the parent table, a foreign key constraint on a REF column references the implicit object identifier column of the parent table.

If you do not specify a constraint name, then Oracle generates a system name for the constraint of the form SYS_Cn.

If you add a referential integrity constraint to an existing REF column that is already scoped, then the referenced table must be the same as the scope table of the REF column. If you later drop the referential integrity constraint, then the REF column will remain scoped to the referenced table.

As is the case for foreign key constraints on other types of columns, you can use the references_clause alone for inline declaration. For out-of-line declaration you must also specify the FOREIGN KEY keywords plus one or more REF columns or attributes.

See Also:

Oracle Database Object-Relational Developer's Guide for more information on object identifiers

Restrictions on Foreign Key Constraints on REF Columns Foreign key constraints on REF columns have the following additional restrictions:

Specifying Constraint State

As part of constraint definition, you can specify how and when Oracle should enforce the constraint.

constraint_state You can use the constraint_state with both inline and out-of-line specification. Specify the clauses of constraint_state in the order shown, from top to bottom, and do not specify any clause more than once.

DEFERRABLE Clause The DEFERRABLE and NOT DEFERRABLE parameters indicate whether or not, in subsequent transactions, constraint checking can be deferred until the end of the transaction using the SET CONSTRAINT(S) statement. If you omit this clause, then the default is NOT DEFERRABLE.

You cannot alter the deferrability of a constraint. Whether you specify either of these parameters, or make the constraint NOT DEFERRABLE implicitly by specifying neither of them, you cannot specify this clause in an ALTER TABLE statement. You must drop the constraint and re-create it.

See Also:

Restriction on [NOT] DEFERRABLE You cannot specify either of these parameters for a view constraint.

INITIALLY Clause The INITIALLY clause establishes the default checking behavior for constraints that are DEFERRABLE. The INITIALLY setting can be overridden by a SET CONSTRAINT(S) statement in a subsequent transaction.

This clause is not valid if you have declared the constraint to be NOT DEFERRABLE, because a NOT DEFERRABLE constraint is automatically INITIALLY IMMEDIATE and cannot ever be INITIALLY DEFERRED.

VALIDATE | NOVALIDATE The behavior of VALIDATE and NOVALIDATE always depends on whether the constraint is enabled or disabled, either explicitly or by default. Therefore they are described in the context of "ENABLE Clause" and "DISABLE Clause".

ENABLE Clause Specify ENABLE if you want the constraint to be applied to the data in the table.

If you enable a unique or primary key constraint, and if no index exists on the key, then Oracle Database creates a unique index. Unless you specify KEEP INDEX when subsequently disabling the constraint, this index is dropped and the database rebuilds the index every time the constraint is reenabled.

You can also avoid rebuilding the index and eliminate redundant indexes by creating new primary key and unique constraints initially disabled. Then create (or use existing) nonunique indexes to enforce the constraint. Oracle does not drop a nonunique index when the constraint is disabled, so subsequent ENABLE operations are facilitated.

If you specify neither VALIDATE nor NOVALIDATE, then the default is VALIDATE.

If you change the state of any single constraint from ENABLE NOVALIDATE to ENABLE VALIDATE, then the operation can be performed in parallel, and does not block reads, writes, or other DDL operations.

Restriction on the ENABLE Clause You cannot enable a foreign key that references a disabled unique or primary key.

DISABLE Clause Specify DISABLE to disable the integrity constraint. Disabled integrity constraints appear in the data dictionary along with enabled constraints. If you do not specify this clause when creating a constraint, then Oracle automatically enables the constraint.

If you specify neither VALIDATE nor NOVALIDATE, then the default is NOVALIDATE.

If you disable a unique or primary key constraint that is using a unique index, then Oracle drops the unique index. Refer to the CREATE TABLE enable_disable_clause for additional notes and restrictions.

RELY Clause  RELY and NORELY are valid only when you are modifying an existing constraint (in the ALTER TABLE ... MODIFY constraint syntax). These parameters specify whether a constraint in NOVALIDATE mode is to be taken into account for query rewrite. Specify RELY to activate an existing constraint in NOVALIDATE mode for query rewrite in an unenforced query rewrite integrity mode. The constraint is in NOVALIDATE mode, so Oracle does not enforce it. The default is NORELY.

Unenforced constraints are generally useful only with materialized views and query rewrite. Depending on the QUERY_REWRITE_INTEGRITY mode, query rewrite can use only constraints that are in VALIDATE mode, or that are in NOVALIDATE mode with the RELY parameter set, to determine join information.

Restriction on the RELY Clause You cannot set a nondeferrable NOT NULL constraint to RELY.

See Also:

Oracle Database Data Warehousing Guide for more information on materialized views and query rewrite

Using Indexes to Enforce Constraints

When defining the state of a unique or primary key constraint, you can specify an index for Oracle to use to enforce the constraint, or you can instruct Oracle to create the index used to enforce the constraint.

using_index_clause You can specify the using_index_clause only when enabling unique or primary key constraints. You can specify the clauses of the using_index_clause in any order, but you can specify each clause only once.

Restrictions on the using_index_clause The following restrictions apply to the using_index_clause:

See Also:

Handling Constraint Exceptions

When defining the state of a constraint, you can specify a table into which Oracle places the rowids of all rows violating the constraint.

exceptions_clause Use the exceptions_clause syntax to define exception handling. If you omit schema, then Oracle assumes the exceptions table is in your own schema. If you omit this clause altogether, then Oracle assumes that the table is named EXCEPTIONS. The EXCEPTIONS table or the table you specify must exist on your local database.

You can create the EXCEPTIONS table using one of these scripts:

If you create your own exceptions table, then it must follow the format prescribed by one of these two scripts.

If you are collecting exceptions from index-organized tables based on primary keys (rather than universal rowids), then you must create a separate exceptions table for each index-organized table to accommodate its primary-key storage. You create multiple exceptions tables with different names by modifying and resubmitting the script.

Restrictions on the exceptions_clause The following restrictions apply to the exceptions_clause:

View Constraints

Data warehousing applications recognize multidimensional data in the Oracle Database by identifying referential integrity constraints in the relational schema. These constraints represent primary and foreign key relationships among tables. By querying the Oracle Database data dictionary, applications can recognize such constraints and therefore recognize the multidimensional data in the database. For schema complexity or security reasons, you might want to define views on fact and dimension tables. Oracle Database provides the ability to constrain these views. By allowing constraint definitions between views, you can propagate base table constraints to the views, thereby allowing applications to recognize multidimensional data even in the restricted environment provided by the view.

Oracle does not enforce view constraints. However, operations on views are subject to the integrity constraints defined on the underlying base tables. This means that you can enforce constraints on views through constraints on base tables.

Notes on View Constraints View constraints are a subset of table constraints and are subject to the following restrictions:

Examples

Unique Key Example The following statement is a variation of the statement that created the sample table sh.promotions. It defines inline and implicitly enables a unique key on the promo_id column (other constraints are not shown):

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

The constraint promo_id_u identifies the promo_id column as a unique key. This constraint ensures that no two promotions in the table have the same ID. However, the constraint does allow promotions without identifiers.

Alternatively, you can define and enable this constraint out of line:

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

The preceding statement also contains the using_index_clause, which specifies storage characteristics for the index that Oracle creates to enable the constraint.

Composite Unique Key Example The following statement defines and enables a composite unique key on the combination of the warehouse_id and warehouse_name columns of the oe.warehouses table:

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

The wh_unq constraint ensures that the same combination of warehouse_id and warehouse_name values does not appear in the table more than once.

The ADD CONSTRAINT clause also specifies other properties of the constraint:

Primary Key Example The following statement is a variation of the statement that created the sample table hr.locations. It creates the locations_demo table and defines and enables a primary key on the location_id column (other constraints from the hr.locations table are omitted):

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

The loc_id_pk constraint, specified inline, identifies the location_id column as the primary key of the locations_demo table. This constraint ensures that no two locations in the table have the same location number and that no location identifier is NULL.

Alternatively, you can define and enable this constraint out of line:

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 Example The following statement alters the locations_demo table (created in "Primary Key Example") to define and enable a NOT NULL constraint on the country_id column:

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

The constraint country_nn ensures that no location in the table has a null country_id.

Composite Primary Key Example The following statement defines a composite primary key on the combination of the prod_id and cust_id columns of the sample table sh.sales:

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

This constraint identifies the combination of the prod_id and cust_id columns as the primary key of the sales table. The constraint ensures that no two rows in the table have the same combination of values for the prod_id column and cust_id columns.

The constraint clause (PRIMARY KEY) also specifies the following properties of the constraint:

Foreign Key Constraint Example The following statement creates the dept_20 table and defines and enables a foreign key on the department_id column that references the primary key on the department_id column of the departments table:

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

The constraint fk_deptno ensures that all departments given for employees in the dept_20 table are present in the departments table. However, employees can have null department numbers, meaning they are not assigned to any department. To ensure that all employees are assigned to a department, you could create a NOT NULL constraint on the department_id column in the dept_20 table in addition to the REFERENCES constraint.

Before you define and enable this constraint, you must define and enable a constraint that designates the department_id column of the departments table as a primary or unique key.

The foreign key constraint definition does not use the FOREIGN KEY clause, because the constraint is defined inline. The data type of the department_id column is not needed, because Oracle automatically assigns to this column the data type of the referenced key.

The constraint definition identifies both the parent table and the columns of the referenced key. Because the referenced key is the primary key of the parent table, the referenced key column names are optional.

Alternatively, you can define this foreign key constraint out of line:

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

The foreign key definitions in both variations of this statement omit the ON DELETE clause, causing Oracle to prevent the deletion of a department if any employee works in that department.

ON DELETE Example This statement creates the dept_20 table, defines and enables two referential integrity constraints, and uses the ON DELETE clause:

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

Because of the first ON DELETE clause, if manager number 2332 is deleted from the employees table, then Oracle sets to null the value of manager_id for all employees in the dept_20 table who previously had manager 2332.

Because of the second ON DELETE clause, Oracle cascades any deletion of a department_id value in the departments table to the department_id values of its dependent rows of the dept_20 table. For example, if Department 20 is deleted from the departments table, then Oracle deletes all of the employees in Department 20 from the dept_20 table.

Composite Foreign Key Constraint Example The following statement defines and enables a foreign key on the combination of the employee_id and hire_date columns of the dept_20 table:

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;

The constraint fk_empid_hiredate ensures that all the employees in the dept_20 table have employee_id and hire_date combinations that exist in the employees table. Before you define and enable this constraint, you must define and enable a constraint that designates the combination of the employee_id and hire_date columns of the employees table as a primary or unique key.

The EXCEPTIONS INTO clause causes Oracle to write information to the wrong_emp table about any rows in the dept_20 table that violate the constraint. If the wrong_emp exceptions table does not already exist, then this statement will fail.

Check Constraint Examples The following statement creates a divisions table and defines a check constraint in each column of the table:

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

Each constraint restricts the values of the column in which it is defined:

Because each CONSTRAINT clause contains the DISABLE clause, Oracle only defines the constraints and does not enable them.

The following statement creates the dept_20 table, defining out of line and implicitly enabling a check constraint:

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

This constraint uses an inequality condition to limit an employee's total commission, the product of salary and commission_pct, to $5000:

Because the constraint clause in this example does not supply a constraint name, Oracle generates a name for the constraint.

The following statement defines and enables a primary key constraint, two foreign key constraints, a NOT NULL constraint, and two check constraints:

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

The constraints enable the following rules on table data:

This example also illustrates the following points about constraint clauses and column definitions:

Attribute-Level Constraints Example The following example guarantees that a value exists for both the first_name and last_name attributes of the name column in the students table:

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 Constraint Examples The following example creates a duplicate of the sample schema object type cust_address_typ, and then creates a table containing a REF column with a SCOPE constraint:

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

The following example creates the same table but with a referential integrity constraint on the REF column that references the object identifier column of the parent table:

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

The following example uses the type department_typ and the table departments_obj_t, created in "Creating Object Tables: Examples". A table with a scoped REF is then created.

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

The following statement creates a table with a REF column which has a referential integrity constraint defined on it:

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

Explicit Index Control Example The following statement shows another way to create a unique (or primary key) constraint that gives you explicit control over the index (or indexes) Oracle uses to enforce the constraint:

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

This example also shows that you can create an index for one constraint and use that index to create and enable another constraint in the same statement.

DEFERRABLE Constraint Examples The following statement creates table games with a NOT DEFERRABLE INITIALLY IMMEDIATE constraint check (by default) on the scores column:

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

To define a unique constraint on a column as INITIALLY DEFERRED DEFERRABLE, issue the following statement:

CREATE TABLE games
  (scores NUMBER, CONSTRAINT unq_num UNIQUE (scores)
   INITIALLY DEFERRED DEFERRABLE);
Go to previous page
Previous		 Go to next page
Next
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Master Index
Master Index		 Go to Feedback page
Contact Us