SQL Extensions   «Prev  Next»

Lesson 6 FOREIGN KEY, CHECK, and UNIQUE constraints
Objective Identify the syntax components of the FOREIGN KEY, CHECK, and UNIQUE constraints

FOREIGN KEY, CHECK, UNIQUE constraints

The three constraints that we have yet to observe are:
  1. FOREIGN KEY CONSTRAINT: This constraint defines a foreign key as a reference to the primary key of another table.
  2. CHECK CONSTRAINT: This constraint allows you to attach validation to a column.

The next two sections describe and give examples of each constraint.

Defining a FOREIGN KEY constraint

A foreign key[1] is defined as a column or list of columns in one table that contain data that references the primary key of another table. The foreign key is a critical concept in relational databases. Defining them within the Oracle database gives your database better capability to enforce data integrity. The diagram below describes the syntax of the FOREIGN KEY constraint.

This list is columns in the current table that make up the foreign key columns.
  1. This list is columns in the current table that make up the foreign key columns.
  2. This list of columns is the primary key columns in the referenced table. You can leave this off if you wish. If left off, Oracle simply assumes that the entire primary key in its original order will be used.
  3. If you include the ON DELETE CASCADE parameter, this means that when the parent row in the referenced table is deleted, the child rows in this table are deleted.


FOREIGN KEY constraint syntax

Foreign Key constraint 
[CONSTRAINT constraintname] FOREIGH KEY (column list)
REFERENCES [user.]table[(column list)]
[ON DELETE {CASCADE | SET NULL} ]

CREATE TABLE PET_CARE_LOG
(PRODUCT_ID NUMBER(10),
LOG_DATETIME DATE,
LOG_TEXT VARCHAR2(500),
CONSTRAINT PET_PRODUCT_FK FOREIGN KEY(PRODUCT_ID)
 REFERENCES PRODUCT(PRODUCT_ID) ON DELETE CASCADE)

column list 1 This list is columns in the current table that make up the foreign key columns.
column list 2 This list of columns is the primary key columns in the referenced table. You can leave this off if you wish. If left off, Oracle simply assumes that the entire primary key in its original order will be used.
ON DELETE{CASCADE If you include the ON DELETE CASCADE parameter, this means that when the parent row in the referenced table is deleted, the child rows in this table are deleted.
SET NULL If you include the ON DELETE SET NULL parameter, when a parent row is deleted, the child rows in this table have their foreign key data set to null values.
] Eliminating the ON DELETE parameter means that the parent row cannot be deleted if there are any child rows in this table.

You can place a FOREIGN KEY constraint within a column specification (rather than at the end of the column list). However, the syntax is somewhat different.

FOREIGN KEY in Column specification

This is a variation on the syntax for creating a FOREIGN KEY constraint. It has several restrictions:
  1. You cannot name the foreign key yourself, you must allow the system to name it.
  2. The foreign key column must be one column.

Here is the syntax: 
REFERENCES [user.]table [(column_name)] 
[ ON DELETE { CASCADE | SET NULL} ]

Here is an example: 
CREATE TABLE PET_CARE_LOG
(PRODUCT_ID NUMBER(10) REFERENCES PRODUCT 
ON DELETE SET NULL, 
LOG_DATETIME DATE, 
LOG_TEXT VARCHAR2(500))

In this example, the PRODUCT_ID is a foreign key referencing the PRODUCT table. When the parent row (in PRODUCT) is deleted, the child row's PRODUCT_ID column is set to null.

Creating Check Constraint

A check constraint[2] is a condition that is required for every row in the table. Each row's data must either have data that complies with the condition or have null values in the checked columns.
The MouseOver below describes the syntax for creating a CHECK constraint.

Check Constraint
Check Constraint

CHECK Constraint Syntax (Duplicate)

Check constraint Syntax
Check constraint Syntax

CONSTRAINT If you leave out CONSTRAINT and constraint name, the system names the constraint for you.
CHECK pop-up caption
Condition The condition parameter can be any valid condition that evaluates to TRUE or FALSE. Think of the WHERE clause in a query. The same type of condition that works in a WHERE clause will work in a CHECK constraint.
TYPE_OF_LOG Add the CHECK constraint to a column specification if the constraint deals only with that column.

There are a few restrictions to the condition you can create in a CHECK constraint:
  1. You can refer to any column inside the same table.
  2. You cannot query or refer to any columns from other tables.
  3. You cannot use pseudocolumns.
  4. You cannot use a query that returns other rows in the same table.
The next lesson looks at how to modify column definitions within an existing table.

Exercise

Click the Exercise link below to match SQL constraints with their descriptions.
foreign Key Check Unique Constraints - Exercise
[1] Foreign key: A column or list of columns in one table that contain data that references the primary key of another table.
[2] Check constraint: A condition that is required for every row in the table.