CREATING INTEGRITY CONSTRAINTS

In the following few sections we will see how to integrity constraints.

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.