Types of constraints

Types of constraints
Constraints can be given at two different levels. If the constraint is related to a single column
the constraint is given at the column level otherwise constraint is to be given at the table
level. Base on the where a constraint is given, constraint are of two types:
Column Constraints
Table Constraints

Column Constraint
A constraint given at the column level is called as Column Constraint. It defines a rule for a
single column. It cannot refer to column other than the column at which it is defined. A typical
example is PRIMARY KEY constraint when a single column is the primary key of the table.

Table Constraint
A constraint given at the table level is called as Table Constraint. It may refer to more than
one column of the table. A typical example is PRIMARY KEY constraint that is used to define
composite primary key. A column level constraint can be given even at the table level, but a
constraint that deals with more than one column must be given only at the table level.
The following is the syntax of CONSTRAINT clause used with CREATE TABLE and ALTER TABLE
commands.
[CONSTRAINT constraint]
{ [NOT] NULL
| {UNIQUE | PRIMARY KEY}
| REFERENCES [schema.] table [(column)]
 [ON DELETE CASCADE]
| CHECK (condition) }

The following is the syntax of table constraint.
[CONSTRAINT constraint]
{ {UNIQUE | PRIMARY KEY} (column [,column] ...)
| FOREIGN KEY (column [,column] ...)
 REFERENCES [schema.] table [(column [,column] ...)]
 [ON DELETE CASCADE]
| CHECK (condition) }
The main difference between column constraint and table constraint is that in table constraint
we have to specify the name of the column for which the constraint is defined whereas in
column constraint it is not required as constraint is given on immediately after the column.

NOT NULL Constraint
Used to prevent any null value from entering into column. This is automatically defined for
column with PRIMARY KEY constraint.
The following example shows how you can define course name as not null column using NOT
NULL constraint.
CREATE TABLE COURSES
( ...,
 name varchar2(20)
 CONSTRAINT courses_name_nn NOT NULL,
 ...
);
CONSTRAINT option is used to given a name to constraint. The convention followed here is
TABLENAME_COLUMN_TYPE. 

PRIMARY KEY Constraint
This constraint is used to define the primary key of the table. A primary key is used to
uniquely identify rows in a table. There can be only one primary key in a table. It may consist
of more than one column. If primary key is consisting of only one column, it can be given as
column constraints otherwise it is to be given as table constraint.
Note: You have to use table constraint to define composite primary key.
Oracle does the following for the column that has PRIMARY KEY constraint.
Creates a unique index to enforce uniqueness. We will discuss about indexes later in this
book.
Defines NOT NULL constraint to prevent null values.
The following example shows how to use PRIMARY KEY constraint at column level.
CREATE TABLE COURSES
( ccode varchar2(5) CONSTRAINT courses_pk PRIMARY KEY,
 ... );

The following example shows how to define composite primary key using PRIMARY KEY
constraint at the table level.
CREATE TABLE COURSE_FACULTY
 ( ...,
 CONSTRAINT COURSE_FACULTY_PK PRIMARY KEY (ccode, faccode)
 );

UNIQUE Constraint
Enforces uniqueness in the given column(s). Oracle automatically creates a unique index for
this column.
The following example creates unique constraint on NAME column of COURSES table.
CREATE TABLE courses
( ... ,
 name varchar2(20)
 CONSTRAINT courses_name_u UNIQUE,
 ... );
If two or more columns collective should be unique then UNIQUE constraint must be given at
the table level.

FOREIGN KEY Constraint
A foreign key is used to join the child table with parent table. FOREIGN KEY constraint is used
to provide referential integrity, which makes sure that the values of a foreign key are derived
from parent key.  It can be defined either at the table level or at the column level.
If a foreign key is defined on the column in child table then Oracle does not allow the parent
row to be deleted, if it contains any child rows. However, if ON DELETE CASCADE option is
given at the time of defining foreign key, Oracle deletes all child rows while parent row is
being deleted.
The following example defines foreign key constraint for CCODE of COURSE_FACULTY table.
CREATE TABLE course_faculty
(ccode varchar2(5)
 CONSTRAINT course_faculty_ccode_fk REFERENCES  courses(ccode),
 ...
);
Note: When the name of the column in the referenced table is same as the foreign key then
column need not be given after the table name. It means  REFERENCES courses in the
above example will suffice.

Table level constraint is used when foreign key is a composite foreign key.
ON DELETE CASCADE option
As mentioned earlier, after a foreign key is defined, Oracle will NOT allow any parent row to be
deleted if it has dependent rows in the child table.
For example, if CCODE in COURSE_FACULTY table is defined as foreign key referencing CCODE
column of COURSES table then it is NOT possible to delete rows from COURSES table if
dependent rows exists in COURSE_FACULTY table.
However, by using ON DELETE CASCADE it is possible to delete all child rows while parent row
is being deleted.
The following code shows how to use ON DELETE CASCADE option.
CREATE TABLE course_faculty
(ccode varchar2(5)
 CONSTRAINT course_faculty_ccode_fk REFERENCES  courses(ccode)
 ON DELETE CASCADE,
 ...
);

CHECK Constraint
Defines the condition that should be satisfied before insertion or updation is done.
The condition used in CHECK constraint may NOT contain:
A reference to pseudo column SYSDATE
Subquery
If it is given as column constraint, it can refer only to current column. But if it is given as table
constraint, it can refer to more than one column of the table. In neither case it can refer to a
column of other tables.
The following example shows how to create CHECK constraint to make sure GRADE column of
COURSE_FACULTY contains letters A, B and C only.

CREATE TABLE course_faculty
 ( ...,
 grade char(1) CONSTRAINT course_faculty_grade_chk
 CHECK ( grade in (‘A’,’B’,’C’) ),
 ...
 );
The above CHECK constraint does not allow any other characters other than A, B and C. It
must be noted that character comparison is always case sensitive. So to ignore case
differences you can convert GRADE to uppercase before comparison made as follows:
CREATE TABLE course_faculty
 ( ...,
 grade char(1) CONSTRAINT course_faculty_grade_chk
 CHECK ( upper(grade)  in (‘A’,’B’,’C’) ),
 ...
 );
The following is an example of CHECK constraint at table level. The constraint makes sure the
starting date (STDATE) of a batch is less than or equal to ending date (ENDDATE) of the
batch.
CREATE TABLE batches
 ( ...,
 CONSTRAINT batches_dates_chk
 CHECK ( stdate <= enddate),
 );