Another SQL post, after a long time
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
create table PROJECT (
PNO number(3) constraint prj pk primary key,
PNAME varchar2(60) unique,
PMGR number(4) not null,
BUDGET number(8,2) not null,
The above query uses the following constraints.
primary key, Unique element of the table
unique, not element cannot be same
Two parameters can also be used in unique constraint.
constraint no same dates unique(PEND, PSTART)
can be used to ensure that no two projects have same
not null ensures that the value inserted to the table is not null
Even default value can also be declared with constraints. e.g.
PSTART date default(’01-JAN-95’)
will be used to have 1st jan 95 as the default date for
There are some more complex constraints, hope lets have one more classroom notes soon.