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.

  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
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.

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 Syntax
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.