Constraints in SQL

The definition of a table may include the specification of integrity constraints. Basically two
types of constraints are provided: column constraints are associated with a single column
whereas table constraints are typically associated with more than one column. However, any
column constraint can also be formulated as a table constraint. In this section we consider only very simple constraints.

The specification of a (simple) constraint has the following form:
[constraint <name>] primary key | unique | not null
A constraint can be named. It is advisable to name a constraint in order to get more meaningful
information when this constraint is violated due to, e.g., an insertion of a tuple that violates
the constraint. If no name is specified for the constraint, Oracle automatically generates a
name of the pattern SYS C<number>.
The two most simple types of constraints have already been discussed: not null and unique.
Probably the most important type of integrity constraints in a database are primary key constraints.
A primary key constraint enables a unique identification of each tuple in a table.
Based on a primary key, the database system ensures that no duplicates appear in a table. For example, for our EMP table, the specification
create table EMP (
EMPNO number(4) constraint pk emp primary key,
. . . );

defines the attribute EMPNO as the primary key for the table. Each value for the attribute EMPNO
thus must appear only once in the table EMP. A table, of course, may only have one primary
key. Note that in contrast to a unique constraint, null values are not allowed.
Example:
We want to create a table called PROJECT to store information about projects. For each
project, we want to store the number and the name of the project, the employee number of
the project’s manager, the budget and the number of persons working on the project, and
the start date and end date of the project. Furthermore, we have the following conditions:
- a project is identified by its project number,
- the name of a project must be unique,
- the manager and the budget must be defined.
Table definition:
create table PROJECT (
PNO number(3) constraint prj pk primary key,
PNAME varchar2(60) unique,
PMGR number(4) not null,
PERSONS number(5),
BUDGET number(8,2) not null,
PSTART date,
PEND date);
A unique constraint can include more than one attribute. In this case the pattern unique(<column
i>, . . . , <column j>) is used. If it is required, for example, that no two projects have the same
start and end date, we have to add the table constraint
constraint no same dates unique(PEND, PSTART)
This constraint has to be defined in the create table command after both columns PEND and
PSTART have been defined. A primary key constraint that includes more than only one column
can be specified in an analogous way.
Instead of a not null constraint it is sometimes useful to specify a default value for an attribute
if no value is given, e.g., when a tuple is inserted. For this, we use the default clause.
Example:
If no start date is given when inserting a tuple into the table PROJECT, the project start
date should be set to January 1st, 1995:
PSTART date default(’01-JAN-95’)
Note: Unlike integrity constraints, it is not possible to specify a name for a default.