Contents /
Previous /
Next
Constrains
Constraints: NOT NULL, UNIQUE, CHECK
Constraints keep user data constrained, thereby helping to prevent
invalid data from being entered into the database.
For example, a column of type DATE constrains the column to valid dates.
NOT NULL
The constraint NOT NULL prevents NULL values from appearing in a column.
<![CDATA[
CREATE TABLE test( col1 INTEGER NOT NULL );
]]>
Insertion of a NULL value, causes the INSERT to fail.
UNIQUE
The UNIQUE constraint prevents duplicate values from appearing in the column.
It is implemented by creating a unique index on a column.
<![CDATA[
CREATE TABLE test (col1 INTEGER UNIQUE);
]]>
Multiple NULL values can be inserted into a UNIQUE column.
UNIQUE constraint may consists of more than one column:
<![CDATA[
CREATE TABLE uniquetest2 (
col1 INTEGER, col2 INTEGER,
UNIQUE (col1, col2) );
]]>
While col1 or col2 themselves may not be unique, the constraint
requires the combination of col1 and col2 to be unique.
CHECK
The CHECK constraint enforces arbitrary column value restrictions,
to a set of values, for example, only positive numbers, or reasonable dates:
<![CDATA[
CREATE TABLE friends (
firstname CHAR(15),
lastname CHAR(20),
city CHAR(15),
state CHAR(2) CHECK (length(trim(state)) = 2),
--Forces the column to be two characters long.
--CHAR() pads the field with spaces,
--so state must be trimed of trailing spaces
--before length() is computed.
age INTEGER CHECK (age >= 0),
gender CHAR(1) CHECK (gender IN ('M','F')),
last_met DATE CHECK (last_met BETWEEN '1950-01-01'
AND CURRENT_DATE),
CHECK (upper(trim(firstname)) != 'ED' OR
upper(trim(lastname)) != 'RIVERS')
);
]]>
The last restriction is implemented as a table-level CHECK constraint.
It forces the table to accept only rows where firstname is not ED or
lastname is not RIVERS. The effect is to prevent Ed Rivers from being
entered into the table. His name will be rejected if it is in
uppercase, lowercase, or mixed case.
Comparing firstname to ED at the column level would have prevented all
EDs from being entered.
CHECK allows NULL values.