SQL CONSTRAINTS – classroom notes – 4

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 SYS C<number>.

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);

The above query uses the following constraints.

1. primary key, Unique element of the table
2. 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 pend and pstart dates.

3. 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 pstart.

There are some more complex constraints, hope lets have one more classroom notes soon.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s