Handout 22

Implementing Constraints in Oracle

---

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, then one is assigned by Oracle.

Picking your own name makes error messages for constraint violations more understandable, and prevents the creation of multiple constraints if the SQL statements
are run more than once.

Examples

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

CREATE TABLE Dept_tab (
    Deptno  NUMBER(3) CONSTRAINT Dept_pkey 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_tab ( 
    Empno    NUMBER(5) CONSTRAINT Emp_pkey PRIMARY KEY, 
    Ename    VARCHAR2(15) NOT NULL, 
    Job      VARCHAR2(10), 
    Mgr      NUMBER(5) CONSTRAINT Mgr_fkey 
                     REFERENCES Emp_tab,
    Hiredate DATE, 
    Sal      NUMBER(7,2), 
    Comm     NUMBER(5,2), 
    Deptno   NUMBER(3) NOT NULL 
             CONSTRAINT dept_fkey REFERENCES Dept_tab ON DELETE CASCADE);

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:

CREATE UNIQUE INDEX I_dept ON Dept_tab(deptno);
ALTER TABLE Dept_tab 
    ADD CONSTRAINT Dept_pkey PRIMARY KEY (deptno); 

ALTER TABLE Emp_tab 
    ADD CONSTRAINT Dept_fkey FOREIGN KEY (Deptno) REFERENCES Dept_tab;
ALTER TABLE Emp_tab MODIFY (Ename VARCHAR2(15) NOT NULL);

Disabling Enabled Constraints

The following statements are examples of statements that disable enabled integrity constraints:

ALTER TABLE Dept_tab  
    DISABLE CONSTRAINT Dname_ukey;
 
ALTER TABLE Dept_tab
    DISABLE PRIMARY KEY
    DISABLE UNIQUE (Dname)
    DISABLE UNIQUE (Loc);

Creating Disabled Constraints

The following CREATE TABLE and ALTER TABLE statements both define and disable integrity constraints: 

CREATE TABLE Emp_tab (
    Empno NUMBER(5) PRIMARY KEY DISABLE);

ALTER TABLE Emp_tab
    ADD PRIMARY KEY (Empno) DISABLE;

Enabling Disabled Constraints

The following statements are examples of statements that enable disabled integrity constraints: 

ALTER TABLE Dept_tab  
    ENABLE CONSTRAINT Dname_ukey; 
 
ALTER TABLE Dept_tab  
    ENABLE PRIMARY KEY  
    ENABLE UNIQUE (Dname)
    ENABLE UNIQUE (Loc);

Listing All of Your Accessible Constraints

The following query lists all constraints defined on all tables accessible to the user: 

SELECT Constraint_name, Constraint_type, Table_name, 
    R_constraint_name 
    FROM User_constraints;

Listing Column Names that Constitute an Integrity Constraint

The following query lists all columns that constitute the constraints defined on all tables accessible to you, the user: 

SELECT Constraint_name, Table_name, Column_name  
    FROM User_cons_columns;

Considering the example statements at the beginning of this section, a list similar to the one below is returned: 

CONSTRAINT_NAME  TABLE_NAME  COLUMN_NAME  
---------------  ----------- ---------------  
DEPT_FKEY        EMP_TAB     DEPTNO  
DNAME_UKEY       DEPT_TAB    DNAME  
DNAME_UKEY       DEPT_TAB    LOC  
LOC_CHECK1       DEPT_TAB    LOC  
MGR_FKEY         EMP_TAB     MGR  
SYS_C00275       DEPT_TAB    DEPTNO  
SYS_C00278       EMP_TAB     ENAME  
SYS_C00279       EMP_TAB     DEPTNO  
SYS_C00280       EMP_TAB     EMPNO 

Primary and Foreign Key Constraints

The most important integrity constraint is the Primary and Foreign Key constraint. This forces coordination between parent and child tables, by checking that every new record inserted in the child table has a corresponding record in the parent table.

For example, to ensure that each customer in the buys table has a corresponding record in the custome_dbf table, first create a PRIMARY KEY constraint on the custno column in custome_dbf. Then create a referential integrity constraint on the custno column in the buys table that references the primary key of the custome_dbf table.

Each table can have one primary key. A primary key allows each row in a table to be uniquely identified and ensures that no duplicate rows exist. The primary key may be a composite key.

Whenever two tables are related by a common column, define a primary key constraint on the column of the parent table and a foreign key constraint on the column in the child table. This maintains the correct relationship between the two tables.

Not Null Constraint

If a column value is mandatory (i.e. a row cannot be created with the column empty), the NOT NULL clause is included in the column description; this has the effect of forcing anyone creating an entry in the table to enter a value for that column.

By default, the PRIMARY KEY is NOT NULL.

You can only specify NOT NULL with a column CONSTRAINT syntax in a CREATE TABLE or ALTER TABLE statement.

Additional constraints on the foreign key will be determined by usage. For example, in the custome_dbf and buys tables, a NOT NULL constraint should be placed on the foreign key as all customers buying a car must have a name and address. However, it is possible, as in the emp, dept sample tables, that an employee does not work for a specific department, so no constraints can be put on the dept table to allow null values. A UNIQUE constraint on a foreign key would be used in a 1:1 relationship to ensure that there were no duplicate records for a particular key.

Setting NOT NULL constraints in columns ensures that foreign key columns cannot be accepted without a value.

Default Value Constraints

Default values are useful for inserting SYSDATE, USER etc., but also any literal or expression.

Unique Key Constraints

UNIQUE keys are used to prevent the duplication of key values within the rows of the table. Do not confuse UNIQUE keys with PRIMARY keys; PRIMARY keys are used to identify each row in the table uniquely. Therefore, unique keys should not have the purpose of identifying rows in the table. Examples of UNIQUE keys are social security numbers, department names, membership numbers etc.

Check Constraints

Use CHECK constraints when you need to enforce integrity rules that can be evaluated based on logical expressions. For example, thay can be used to check against a fixed set of values. A check integrity constraint contains a boolean expression that can be evaluated using the values in a row being inserted or updated. The expression must evaluate to true or NULL for every row in the table, and rows which do not meet this condition will be rejected.

Additional Examples

The following examples illustrate how to use column and table constraints, with and without specifying constraint identifiers.

Example 1

Using simple column constraints to define primary and foreign keys.

Uses a mixture of column and table constraints with and without constraint identifiers.

Referential integrity constraints with composite keys. To define a composite primary or foreign key, you must use the table constraint syntax.