CONSTRAINT clause

Purpose

Prerequisites

Defining a constraint may also require additional privileges or preconditions that depend on the type of constraint. For information on these privileges, see the descriptions of each type of integrity constraint beginning .

Syntax

Syntax

Keywords and Parameters

CONSTRAINT

identifies the integrity constraint by the name constraint. Oracle7 stores this name in the data dictionary along with the definition of the integrity constraint. If you omit this identifier, Oracle7 generates a name with this form:

SYS_Cn

where

n is an integer that makes the name unique within the database. For the names and definitions of integrity constraints, query the data dictionary. For information on data dictionary views that contain constraints, see the "Data Dictionary Reference" chapter of Oracle7 Server Reference.

NULL

specifies that a column can contain null values.

NOT NULL

specifies that a column cannot contain null values.

If you do not specify NULL or NOT NULL in a column definition, NULL is the default.

UNIQUE

designates a column or combination of columns as a unique key.

PRIMARY KEY

designates a column or combination of columns as the table's primary key.

FOREIGN KEY

designates a column or combination of columns as the foreign key in a referential integrity constraint.

REFERENCES

identifies the primary or unique key that is referenced by a foreign key in a referential integrity constraint.

ON DELETE CASCADE

specifies that Oracle7 maintains referential integrity by automatically removing dependent foreign key values if you remove a referenced primary or unique key value.

CHECK

specifies a condition that each row in the table must satisfy.

USING INDEX

specifies parameters for the index Oracle7 uses to enforce a UNIQUE or PRIMARY KEY constraint. The name of the index is the same as the name of the constraint. You can choose the values of the INITRANS, MAXTRANS, TABLESPACE, STORAGE, PCTFREE, RECOVERABLE, and UNRECOVERABLE parameters for the index. For information on these parameters, see the CREATE TABLE command .

Only use this clause when enabling UNIQUE and PRIMARY KEY constraints.

NOSORT

indicates that the rows are stored in the database in ascending order and therefore Oracle7 does not have to sort the rows when creating the index.

DISABLE

disables the integrity constraint. If an integrity constraint is disabled, Oracle7 does not enforce it. If you do not specify this option, Oracle7 automatically enables the integrity constraint.

You can also enable and disable integrity constraints with the ENABLE and DISABLE clauses of the CREATE TABLE and ALTER TABLE commands. See the ENABLE clause and DISABLE clause .

Defining Integrity Constraints

table_constraint

The table_constraint syntax is part of the table definition. An integrity constraint defined with this syntax can impose rules on any columns in the table.

The table_constraint syntax can appear in a CREATE TABLE or ALTER TABLE statement. This syntax can define any type of integrity constraint except a NOT NULL constraint.

column_constraint

The column_constraint syntax is part of a column definition. Usually, an integrity constraint defined with this syntax can only impose rules on the column in which it is defined.

The column_constraint syntax that appears in a CREATE TABLE statement can define any type of integrity constraint. Column_constraint syntax that appears in an ALTER TABLE statement can only define or remove a NOT NULL constraint.

The table_constraint syntax and the column_constraint syntax are simply different syntactic means of defining integrity constraints. A constraint that references more than one column must be defined as a table constraint. There is no other functional difference between an integrity constraint defined with table_constraint syntax and the same constraint defined with column_constraint syntax.

NOT NULL Constraints

The NULL keyword indicates that a column can contain nulls. It does not actually define an integrity constraint. If you do not specify either NOT NULL or NULL, the column can contain nulls by default.

You can only specify NOT NULL or NULL with column_constraint syntax in a CREATE TABLE or ALTER TABLE statement, not with table_constraint syntax.

Example I

The following statement alters the EMP table and defines and enables a NOT NULL constraint on the SAL column:

ALTER TABLE emp 

	MODIFY (sal  NUMBER  CONSTRAINT nn_sal NOT NULL) 

NN_SAL ensures that no employee in the table has a null salary.

UNIQUE Constraints

A unique key column cannot be of datatype LONG or LONG RAW. You cannot designate the same column or combination of columns as both a unique key and a primary key or as both a unique key and a cluster key. However, you can designate the same column or combination of columns as both a unique key and a foreign key.

Defining Unique Keys

Example II

The following statement creates the DEPT table and defines and enables a unique key on the DNAME column:

CREATE TABLE dept 
	(deptno  NUMBER(2), 
	 dname   VARCHAR2(9)  CONSTRAINT unq_dname UNIQUE,
	 loc     VARCHAR2(10) ) 

The constraint UNQ_DNAME identifies the DNAME column as a unique key. This constraint ensures that no two departments in the table have the same name. However, the constraint does allow departments without names.

Alternatively, you can define and enable this constraint with the table_constraint syntax:

CREATE TABLE dept 
	(deptno  NUMBER(2), 
	 dname   VARCHAR2(9), 
	 loc     VARCHAR2(10), 
		CONSTRAINT unq_dname 
		UNIQUE (dname) 
	USING INDEX PCTFREE 20
		TABLESPACE user_x
		STORAGE (INITIAL 8K  NEXT 6K) ) 

The above statement also uses the USING INDEX option to specify storage characteristics for the index that Oracle7 creates to enforce the constraint.

Defining Composite Unique Keys

To satisfy a constraint that designates a composite unique key, no two rows in the table can have the same combination of values in the key columns. Also, 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.

Example III

The following statement defines and enables a composite unique key on the combination of the CITY and STATE columns of the CENSUS table:

ALTER TABLE census 
	ADD CONSTRAINT unq_city_state 
	UNIQUE (city, state) 
	USING INDEX PCTFREE 5  
		TABLESPACE user_y
	EXCEPTIONS INTO bad_keys_in_ship_cont 

The UNQ_CITY_STATE constraint ensures that the same combination of CITY and STATE values does not appear in the table more than once.

The CONSTRAINT clause also specifies other properties of the constraint:

• The USING INDEX option specifies storage characteristics for the index Oracle7 creates to enforce the constraint.

• The EXCEPTIONS option causes Oracle7 to write information to the BAD_KEYS_IN_SHIP_CONT table about any rows currently in the SHIP_CONT table that violate the constraint.

PRIMARY KEY Constraints

• No primary key value can appear in more than one row in the table.

• No column that is part of the primary key can contain a null.

A primary key column cannot be of datatype LONG or LONG RAW. You cannot designate the same column or combination of columns as both a primary key and a unique key or as both a primary key and a cluster key. However, you can designate the same column or combination of columns as both a primary key and a foreign key.

Defining Primary Keys

Example IV

The following statement creates the DEPT table and defines and enables a primary key on the DEPTNO column:

CREATE TABLE dept 
	(deptno  NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY, 
	 dname   VARCHAR2(9), 
	 loc     VARCHAR2(10) ) 

The PK_DEPT constraint identifies the DEPTNO column as the primary key of the DEPTNO table. This constraint ensures that no two departments in the table have the same department number and that no department number is NULL.

Alternatively, you can define and enable this constraint with table_constraint syntax:

CREATE TABLE dept 
	(deptno  NUMBER(2), 
	 dname   VARCHAR2(9), 
	 loc   VARCHAR2(10), 
	 CONSTRAINT pk_dept PRIMARY KEY (deptno) ) 

Defining Composite Primary Keys

Example V

The following statement defines a composite primary key on the combination of the SHIP_NO and CONTAINER_NO columns of the SHIP_CONT table:

ALTER TABLE ship_cont 
	ADD PRIMARY KEY (ship_no, container_no) DISABLE 

This constraint identifies the combination of the SHIP_NO and CONTAINER_NO columns as the primary key of the SHIP_CONTAINER. The constraint ensures that no two rows in the table have the same values for both the SHIP_NO column and the CONTAINER_NO column.

The CONSTRAINT clause also specifies the following properties of the constraint:

• Since the constraint definition does not include a constraint name, Oracle7 generates a name for the constraint.

• The DISABLE option causes Oracle7 to define the constraint but not enforce it.

Referential Integrity Constraints

• The child and parent tables must be on the same database. They cannot be on different nodes of a distributed database. Oracle7 allows you to enforce referential integrity across nodes of a distributed database with database triggers. For information on how to use database triggers for this purpose, see the "Using Database Triggers" chapter of the Oracle7 Server Application Developer's Guide.

• The foreign key and the referenced key can be in the same table. In this case, the parent and child tables are the same.

• The value of the row's foreign key must appear as a referenced key value in one of the parent table's rows. The row in the child table is said to depend on the referenced key in the parent table.

• The value of one of the columns that makes up the foreign key must be null.

FOREIGN KEY

identifies the column or combination of columns in the child table that makes up of the foreign key. Only use this keyword when you define a foreign key with a table constraint clause.

REFERENCES

identifies the parent table and the column or combination of columns that make up the referenced key.

If you only identify the parent table and omit the column names, the foreign key automatically references the primary key of the parent table.

The corresponding columns of the referenced key and the foreign key must match in number and datatypes.

ON DELETE CASCADE

allows deletion of referenced key values in the parent table that have dependent rows in the child table and causes Oracle7 to automatically delete dependent rows from the child table to maintain referential integrity.

If you omit this option, Oracle7 forbids deletions of referenced key values in the parent table that have dependent rows in the child table.

Before you define a referential integrity constraint in the child table, the referenced UNIQUE or PRIMARY KEY constraint on the parent table must already be defined. Also, the parent table must be in your own schema or you must have REFERENCES privilege on the columns of the referenced key in the parent table. Before you enable a referential integrity constraint, its referenced constraint must be enabled.

You cannot define a referential integrity constraint in a CREATE TABLE statement that contains an AS clause. Instead, you can create the table without the constraint and then add it later with an ALTER TABLE statement.

A foreign key column cannot be of datatype LONG or LONG RAW. 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. Also, a single column can be part of more than one foreign key.

Defining Referential Integrity Constraints

Example VI

The following statement creates the EMP table and defines and enables a foreign key on the DEPTNO column that references the primary key on the DEPTNO column of the DEPT table:

CREATE TABLE emp 

	(empno     NUMBER(4), 

	 ename     VARCHAR2(10), 

	 job       VARCHAR2(9), 

	 mgr       NUMBER(4), 

	 hiredate  DATE, 

	 sal       NUMBER(7,2), 

	 comm      NUMBER(7,2), 

	 deptno    CONSTRAINT fk_deptno REFERENCES dept(deptno) ) 

The constraint FK_DEPTNO ensures that all departments given for employees in the EMP table are present in the DEPT table. However, employees can have null department numbers, meaning they are not assigned to any department. If you wished to prevent the latter, you could create a NOT NULL constraint on the deptno column in the EMP table, in addition to the REFERENCES constraint.

Before you define and enable this constraint, you must define and enable a constraint that designates the DEPTNO column of the DEPT table as a primary or unique key. For the definition of such a constraint, see Example IV .

Note that the referential integrity constraint definition does not use the FOREIGN KEY keyword to identify the columns that make up the foreign key. Because the constraint is defined with a column constraint clause on the DEPTNO column, the foreign key is automatically on the DEPTNO column.

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

Note that the above statement omits the DEPTNO column's datatype. Because this column is a foreign key, Oracle7 automatically assigns it the datatype of the DEPT.DEPTNO column to which the foreign key refers.

Alternatively, you can define a referential integrity constraint with table_constraint syntax:

CREATE TABLE emp 
  (empno     NUMBER(4), 
   ename     VARCHAR2(10), 
   job       VARCHAR2(9), 
   mgr       NUMBER(4), 
   hiredate  DATE, 
   sal       NUMBER(7,2), 
   comm      NUMBER(7,2), 
   deptno, 
   CONSTRAINT fk_deptno 
      FOREIGN KEY (deptno) 
   REFERENCES dept(deptno) ) 

Note that the foreign key definitions in both of the above statements omit the ON DELETE CASCADE option, causing Oracle7 to forbid the deletion of a department if any employee works in that department.

Maintaining Referential Integrity with the ON DELETE CASCADE Option

Example VII

This example creates the EMP table, defines and enables the referential integrity constraint FK_DEPTNO, and uses the ON DELETE CASCADE option:

CREATE TABLE emp 
	(empno		NUMBER(4), 
	ename		VARCHAR2(10), 
	job		VARCHAR2(9), 
	mgr		NUMBER(4), 
	hiredate	DATE, 
	sal		NUMBER(7,2), 
	comm		NUMBER(7,2), 
	deptno		NUMBER(2) 	CONSTRAINT fk_deptno 
							REFERENCES dept(deptno) 
							ON DELETE CASCADE ) 

Because of the ON DELETE CASCADE option, Oracle7 cascades any deletion of a DEPTNO value in the DEPT table to the DEPTNO values of its dependent rows of the EMP table. For example, if department 20 is deleted from the DEPT table, Oracle7 deletes the department's employees from the EMP table.

Referential Integrity Constraints with Composite Keys

To satisfy a referential integrity constraint involving composite keys, each row in the child table must satisfy one of the following conditions:

• The values of the foreign key columns must match the values of the referenced key columns in a row in the parent table.

• The value of at least one of the columns of the foreign key must be null.

The following statement defines and enables a foreign key on the combination of the AREACO and PHONENO columns of the PHONE_CALLS table:

ALTER TABLE phone_calls 

   ADD CONSTRAINT fk_areaco_phoneno 

      FOREIGN KEY (areaco, phoneno) 

      REFERENCES customers(areaco, phoneno)

      EXCEPTIONS INTO wrong_numbers 

The constraint FK_AREACO_PHONENO ensures that all the calls in the PHONE_CALLS table are made from phone numbers that are listed in the CUSTOMERS table. Before you define and enable this constraint, you must define and enable a constraint that designates the combination of the AREACO and PHONENO columns of the CUSTOMERS table as a primary or unique key.

The EXCEPTIONS option causes Oracle7 to write information to the WRONG_NUMBERS about any rows in the PHONE_CALLS table that violate the constraint.

CHECK Constraints

• queries to refer to values in other rows

• calls to the functions SYSDATE, UID, USER, or USERENV

• the pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM

• date constants that are not fully specified

If you create multiple CHECK constraints for a column, design them carefully so their purposes do not conflict. Oracle7 does not verify that CHECK conditions are not mutually exclusive.

Example IX

The following statement creates the DEPT table and defines a CHECK constraint in each of the table's columns:

CREATE TABLE dept	(deptno NUMBER	CONSTRAINT check_deptno
					CHECK (deptno BETWEEN 10 AND 99) 
					DISABLE, 
dname VARCHAR2(9)	CONSTRAINT check_dname 
					CHECK (dname = UPPER(dname)) 
					DISABLE, 
loc VARCHAR2(10)	CONSTRAINT check_loc 
					CHECK (loc IN ('DALLAS','BOSTON',
					'NEW YORK','CHICAGO')) 
					DISABLE) 

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

CHECK_DEPTNO

ensures that no department numbers are less than 10 or greater than 99.

CHECK_DNAME

ensures that all department names are in uppercase.

CHECK_LOC

restricts department locations to Dallas, Boston, New York, or Chicago.

Unlike other types of constraints, a CHECK constraint defined with column_constraint syntax can impose rules on any column in the table, rather than only on the column in which it is defined.

Because each CONSTRAINT clause contains the DISABLE option, Oracle7 only defines the constraints and does not enforce them.

Example X

The following statement creates the EMP table and uses a table constraint clause to define and enable a CHECK constraint:

CREATE TABLE emp 

  (empno     NUMBER(4), 

   ename     VARCHAR2(10), 

   job       VARCHAR2(9), 

   mgr       NUMBER(4), 

   hiredate  DATE, 

   sal       NUMBER(7,2), 

   comm      NUMBER(7,2),

   deptno    NUMBER(2),
   CHECK (sal + comm <= 5000) )

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

• If an employee has non-null values for both salary and commission, the sum of these values must not be more than $5000 to satisfy the constraint.

• If an employee has a null salary or commission, the result of the condition is unknown and the employee automatically satisfies the constraint.

Example XI

The following statement defines and enables a PRIMARY KEY constraint, two referential integrity 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 scott.order (order_id), 
	 part_no  NUMBER 
			  CONSTRAINT fk_pno REFERENCES scott.part (part_no), 
	 quantity NUMBER 
	          CONSTRAINT nn_qty NOT NULL 
	          CONSTRAINT check_qty_low CHECK (quantity > 0), 
	 cost     NUMBER 
	          CONSTRAINT check_cost CHECK (cost > 0) ) 

The constraints enforce the following rules on table data:

PK_OD

identifies the combination of the ORDER_ID and PART_NO columns as the primary key of the table. To satisfy this constraint, the following conditions must be true:

• No two rows in the table can contain the same combination of values in the ORDER_ID and the PART_NO columns.

• No row in the table can have a null in either the ORDER_ID column or the PART_NO column.

FK_OID

identifies the ORDER_ID column as a foreign key that references the ORDER_ID column in the ORDER table in SCOTT's schema. All new values added to the column ORDER_DETAIL.ORDER_ID must already appear in the column SCOTT.ORDER.ORDER_ID.

FK_PNO

identifies the PART_NO column as a foreign key that references the PART_NO column in the PART table owned by SCOTT. All new values added to the column ORDER_DETAIL.PART_NO must already appear in the column SCOTT.PART.PART_NO.

NN_QTY

forbids nulls in the QUANTITY column.

CHECK_QTY

ensures that values in the QUANTITY column are always greater than 0.

CHECK_COST

ensures the values in the COST column are always greater than 0.

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

• Table_constraint syntax and column definitions can appear in any order. In this example, note that the table_constraint syntax that defines the PK_OD constraint precedes the column definitions. In Example IV in this section, the table_constraint syntax defining the table's primary key follows the column definitions.

• A column definition can use column_constraint syntax multiple times. In this example, the definition of the QUANTITY column contains the definitions of both the NN_QTY and CHECK_QTY constraints.

• A table can have multiple CHECK constraints. Multiple CHECK constraints, each with a simple condition enforcing a single business rule is better than a single CHECK constraint with a complicated condition enforcing multiple business rules. When a constraint is violated, Oracle7 returns an error message identifying the constraint. Such an error message more precisely identifies the violated business rule if the identified constraint enforces a single business rule.

Related Topics