Part 1: Constraints in SQL

Purpose:

Use a constraint to define an integrity constraint—a rule that restricts the values in a database. 

The Oracle server uses constraints to prevent invalid data entry into tables. 

You can use constraints to do the following:

•	Enforce rules on the data in a table whenever a row is inserted, updated, or deleted from that table. The constraint must be satisfied for the operation to succeed.

•	Prevent the deletion of a table if there are dependencies from other tables.

•	Provide rules for Oracle tools, such as Oracle Developer.

The six types of integrity constraint are described briefly here:

• A NOT NULL constraint prohibits a database value from being null.

• A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.

• A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. It prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.

• A foreign key constraint requires values in one table to match values in another table.

• A check constraint requires a value in the database to comply with a specified condition.

• A REF column by definition references an object in another object type or in a relational table. A REF constraint lets you further describe the relationship between the REF column and the object it references.

You can define constraints syntactically in two ways:

• As part of the definition of an individual column or attribute. This is called inline specification.

• As part of the table definition. This is called out-of-line specification.

NOT NULL constraints must be declared inline. All other constraints can be declared either inline or out of line.

Constraint clauses can appear in the following statements:

• CREATE TABLE
• ALTER TABLE 
• CREATE VIEW 
• ALTER VIEW 

Prerequisites:

You must have the privileges necessary to issue the statement in which you are defining the constraint.

To create a foreign key constraint, in addition, the parent table or view must be in your own schema or you must have the REFERENCES privilege on the columns of the referenced key in the parent table or view.

Oracle Database does not support constraints on columns or attributes whose type is a user-defined object, nested table, VARRAY, REF, or LOB, with two exceptions:

• NOT NULL constraints are supported for a column or attribute whose type is userdefined object, VARRAY, REF, or LOB.
• NOT NULL, foreign key, and REF constraints are supported on a column of type REF.

CONSTRAINT constraint_name

You can name a constraint, or the Oracle server generates a name by using the SYS_Cn format.

Create a constraint at either of the following times:

–	At the same time as the creation of the table
–	After the creation of the table

Define a constraint at the column or table level. View a constraint in the data dictionary.

Constraints can be defined at the time of table creation or after the creation of the table. You can define a constraint at the column or table level. Functionally, a table-level constraint is the same as a column-level constraint.

CREATE TABLE [schema.]table
(column datatype [DEFAULT expr] [column_constraint],
...
[table_constraint][,...]);

Column-level constraint syntax:

column [CONSTRAINT constraint_name] constraint_type,

CREATE TABLE employees( employee_id	NUMBER(6)
CONSTRAINT emp_emp_id_pk PRIMARY KEY,
first_name	VARCHAR2(20),
...);

Table-level constraint syntax:

column,...
[CONSTRAINT constraint_name] constraint_type
(column, ...),

CREATE TABLE employees(employee_id NUMBER(6), first_name VARCHAR2(20),
...
job_id VARCHAR2(10) NOT NULL,
CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID));