Oracle7 Server Application Developer's Guide

Defining Integrity Constraints

Define an integrity constraint using the constraint clause of the SQL commands CREATE TABLE or ALTER TABLE. The next two sections describe how to use these commands to define integrity constraints.

There are additional considerations if you are using Trusted Oracle; see the Trusted Oracle7 Server Administrator's Guide for more information.

The CREATE TABLE Command

The following examples of CREATE TABLE statements show the definition of several integrity constraints:

CREATE TABLE dept (
   deptno  NUMBER(3) PRIMARY KEY, 
   dname   VARCHAR2(15), 
   loc     VARCHAR2(15), 
            CONSTRAINT dname_ukey UNIQUE (dname, loc), 
            CONSTRAINT loc_check1 
                CHECK (loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')));
CREATE TABLE emp ( 
   empno    NUMBER(5) PRIMARY KEY, 
   ename    VARCHAR2(15) NOT NULL, 
   job      VARCHAR2(10), 
   mgr      NUMBER(5) CONSTRAINT mgr_fkey 
                 REFERENCES emp,
   hiredate DATE, 
   sal      NUMBER(7,2), 
   comm     NUMBER(5,2), 
   deptno   NUMBER(3) NOT NULL 
               CONSTRAINT dept_fkey 
               REFERENCES dept ON DELETE CASCADE);

The ALTER TABLE Command

You can also define integrity constraints using the constraint clause of the ALTER TABLE command. For example, the following examples of ALTER TABLE statements show the definition of several integrity constraints:

ALTER TABLE dept 
   ADD PRIMARY KEY (deptno); 

 ALTER TABLE emp 
   ADD CONSTRAINT dept_fkey FOREIGN KEY (deptno) REFERENCES dept 
   MODIFY (ename VARCHAR2(15) NOT NULL);

Restrictions with the ALTER TABLE Command

Because data is likely to be in the table at the time an ALTER TABLE statement is issued, there are several restrictions to be aware of. Table 6 - 1 lists each type of constraint and the associated restrictions with the ALTER TABLE command.

Type of Constraint Added to Existing Columns of the Table Added with New Columns to the Table
NOT NULL Cannot be defined if any row contains a null value for this column* Cannot be defined if the table contains any rows
UNIQUE Cannot be defined if duplicate values exist in the key* Always OK
PRIMARY KEY Cannot be defined if duplicate or null values exist in the key^* Cannot be defined if the table contains any rows
FOREIGN KEY Cannot be defined if the foreign key has values that do not reference a parent key value^* Always OK
CHECK Cannot be defined if the volume has values that do not comply with the check condition^* Always OK

Table 6 - 1. Restrictions for Defining Integrity Constraints with the ALTER TABLE Command

* Assumes DISABLE clause not included in statement.

If you attempt to define a constraint with an ALTER TABLE statement and violate one of these restrictions, the statement is rolled back and an informative error is returned explaining the violation.

Required Privileges

The creator of a constraint must have the ability to create tables (that is, the CREATE TABLE or CREATE ANY TABLE system privilege) or the ability to alter the table (that is, the ALTER object privilege for the table or the ALTER ANY TABLE system privilege) with the constraint. Additionally, UNIQUE key and PRIMARY KEY integrity constraints require that the owner of the table have either a quota for the tablespace that contains the associated index or the UNLIMITED TABLESPACE system privilege. FOREIGN KEY integrity constraints also require some additional privileges; see "Privileges Required for FOREIGN KEY Integrity Constraints"

for specific information.

Naming Integrity Constraints

Assign names to NOT NULL, UNIQUE KEY, PRIMARY KEY, FOREIGN KEY, and CHECK constraints using the CONSTRAINT option of the constraint clause. This name must be unique with respect to other constraints that you own. If you do not specify a constraint name, one is assigned by Oracle.

See the previous examples of the CREATE TABLE and ALTER TABLE statements for examples of the CONSTRAINT option of the Constraint clause. Note that the name of each constraint is included with other information about the constraint in the data dictionary. Refer to the section "Listing Integrity Constraint Definitions" for examples of data dictionary views.

Enabling and Disabling Constraints Upon Definition

By default, whenever an integrity constraint is defined in a CREATE or ALTER TABLE statement, the constraint is automatically enabled (enforced) by Oracle unless it is specifically created in a disabled state using the DISABLE clause. Refer to the section "Enabling and Disabling Integrity Constraints"

for more information about important issues for enabling and disabling constraints.

UNIQUE Key, PRIMARY KEY, and FOREIGN KEY

When defining UNIQUE key, PRIMARY KEY, and FOREIGN KEY integrity constraints, you should be aware of several important issues and prerequisites. For more information about defining and managing FOREIGN KEY constraints, see "Managing FOREIGN KEY Integrity Constraints"

. UNIQUE key and PRIMARY KEY constraints are usually enabled by the database administrator; see the Oracle7 Server Administrator's Guide for more information.

Type of Constraint	Added to Existing Columns of the Table	Added with New Columns to the Table
NOT NULL	Cannot be defined if any row contains a null value for this column*	Cannot be defined if the table contains any rows
UNIQUE	Cannot be defined if duplicate values exist in the key*	Always OK
PRIMARY KEY	Cannot be defined if duplicate or null values exist in the key^*	Cannot be defined if the table contains any rows
FOREIGN KEY	Cannot be defined if the foreign key has values that do not reference a parent key value^*	Always OK
CHECK	Cannot be defined if the volume has values that do not comply with the check condition^*	Always OK