Introduction of Integrity Constraints

We will discuss about two types of constraints that can be specified within the create table statement:check constraints (to restrict possible attribute values), and foreign key constraints (to specify interdependencies between relations).

Check Constraints – Columns in a table must have values that are within a certain range or that satisfy certain conditions. Check constraints allow users to restrict possible attribute values for a column to admissible ones. They can be specified as column constraints or table constraints. The syntaxfor a check constraint is

[constraint ] check()

If a check constraint is specified as a column constraint, the condition can only refer that column.

Example: The name of an employee must consist of upper case letters only; the minimum
salary of an employee is 500; department numbers must range between 10 and 100:

create table EMP
( . . . ,
ENAME varchar2(30) constraint check name
check(ENAME = upper(ENAME) ),
SAL number(5,2) constraint check sal check(SAL >= 500),
DEPTNO number(3) constraint check deptno
check(DEPTNO between 10 and 100) );

If a check constraint is specified as a table constraint, can refer to all columns of the table. Note that only simple conditions are allowed. For example, it is not allowed to refer to columns of other tables or to formulate queries as check conditions. Furthermore, the functions sysdate and user cannot be used in a condition. In principle, thus only simple attribute comparisons and logical connectives such as and, or, and not are allowed. A check condition, however, can include a not null constraint:

SAL number(5,2) constraint check sal check(SAL is not null and SAL >= 500),

Without the not null condition, the value null for the attribute SAL would not cause a violation
of the constraint.

Example: At least two persons must participate in a project, and the project’s start date
must be before the project’s end date:
create table PROJECT
( . . . ,
PERSONS number(5) constraint check pers check (PERSONS > 2),
. . . ,
constraint dates ok check(PEND > PSTART) );

In this table definition, check pers is a column constraint and dates ok is a table constraint.
The database system automatically checks the specified conditions each time a database modification is performed on this relation. For example, the insertion

insert into EMP values(7999,’SCOTT’,’CLERK’,7698,’31-OCT-94’,450,10);
causes a constraint violation
ORA-02290: check constraint (CHECK SAL) violated
and the insertion is rejected.

Foreign Key Constraints – A foreign key constraint (or referential integrity constraint) can be specified as a column constraint or as a table constraint:

[constraint ] [foreign key ()] references [()] [on delete cascade]

This constraint specifies a column or a list of columns as a foreign key of the referencing table.
The referencing table is called the child-table, and the referenced table is called the parent-table.
In other words, one cannot define a referential integrity constraint that refers to a table R before
that table R has been created.

The clause foreign key has to be used in addition to the clause references if the foreign key includes more than one column. In this case, the constraint has to be specified as a table
constraint. The clause references defines which columns of the parent-table are referenced. If
only the name of the parent-table is given, the list of attributes that build the primary key of
that table is assumed.

Example: Each employee in the table EMP must work in a department that is contained in the table DEPT:

create table EMP
( EMPNO number(4) constraint pk emp primary key,
. . . ,
DEPTNO number(3) constraint fk deptno references DEPT(DEPTNO) );

The column DEPTNO of the table EMP (child-table) builds the foreign key and references the
primary key of the table DEPT (parent-table).

It is very important that a foreign key must refer to the complete primary key of a parent-table, not only a subset of the attributes that build the primary key.

DEPTNO DEPTNO
EMP (Child−Table) DEPT (Parent−Table)
. . .

Foreign Key Constraint between the Tables EMP and DEPT

In order to satisfy a foreign key constraint, each row in the child-table has to satisfy one of the
following two conditions:

• the attribute value (list of attribute values) of the foreign key must appear as a primary
key value in the parent-table, or
• the attribute value of the foreign key is null (in case of a composite foreign key, at least
one attribute value of the foreign key is null )

According to the above definition for the table EMP, an employee must not necessarily work in
a department, i.e., for the attribute DEPTNO the value null is admissible.

Example: Each project manager must be an employee:
create table PROJECT
( PNO number(3) constraint prj pk primary key,
PMGR number(4) not null
constraint fk pmgr references EMP,
. . . );

Because only the name of the parent-table is given (DEPT), the primary key of this relation is assumed. A foreign key constraint may also refer to the same table, i.e., parent-table and child-table are identical.
Example: Each manager must be an employee:
create table EMP
( EMPNO number(4) constraint emp pk primary key,
. . .
MGR number(4) not null
constraint fk mgr references EMP,
. . .
);

More about Column- and Table Constraints – If a constraint is defined within the create table command or added using the alter table command, the constraint is automatically enabled. A constraint can be disabled using the command

alter table disable
constraint | primary key | unique[]
[cascade];

To disable a primary key, one must disable all foreign key constraints that depend on this
primary key. The clause cascade automatically disables foreign key constraints that depend
on the (disabled) primary key.

Example: Disable the primary key of the table DEPT and disable the foreign key constraint
in the table EMP:

alter table DEPT disable primary key cascade;

In order to enable an integrity constraint, the clause enable is used instead of disable. A constraint can only be enabled successfully if no tuple in the table violates the constraint. Otherwise an error message is displayed. Note that for enabling/disabling an integrity constraint it is important that you have named the constraints. In order to identify those tuples that violate an integrity constraint whose activation failed, one can use the clause exceptions into EXCEPTIONS with the alter table statement. EXCEPTIONS is a table that stores information about violating tuples.3 Each tuple in this table is identified by the attribute ROWID. Every tuple in a database has a pseudo-column ROWID that is used to identify tuples. Besides the rowid, the name of the table, the table owner as well as the name of the violated constraint are stored.

Example: Assume we want to add an integrity constraint to our table EMP which requires
that each manager must earn more than 4000:
alter table EMP add constraint manager sal
check(JOB != ’MANAGER’ or SAL >= 4000)
exceptions into EXCEPTIONS;
If the table EMP already contains tuples that violate the constraint, the constraint cannot
be activated and information about violating tuples is automatically inserted into the table
EXCEPTIONS.
Detailed information about the violating tuples can be obtained by joining the tables EMP and
EXCEPTIONS, based on the join attribute ROWID:

select EMP.*, CONSTRAINT from EMP, EXCEPTIONS
where EMP.ROWID = EXCEPTIONS.ROW ID;

Before this table can be used, it must be created using the SQL script utlexcept.sql which can be found in the directory $ORACLE HOME/rdbms/admin.

Tuples contained in the query result now can be modified (e.g., by increasing the salary of
managers) such that adding the constraint can be performed successfully. Note that it is important to delete “old” violations from the relation EXCEPTIONS before it is used again. If a table is used as a reference of a foreign key, this table can only be dropped using the command drop table cascade constraints;. All other database objects that refer to this table remain in the database system, but they are not valid.

Information about integrity constraints, their status (enabled, disabled) etc. is stored in the
data dictionary, more precisely, in the tables USER CONSTRAINTS and USER CONS CONSTRAINTS.