|
|
|
Description
Specifies a constraint for a column that restricts the values that the column can store. INSERT, UPDATE, or DELETE statements that violate the constraint fail. FairCom DB SQL returns a constraint violation error with SQLCODE -20116.
Column constraints are similar to table constraints but their definitions are associated with a single column.
Syntax
column_constraint ::
UNIQUE
| NOT NULL [ PRIMARY KEY | UNIQUE ]
| REFERENCES [ owner_name. ] table_name [ ( column_name ) ]
| CHECK ( search_condition )
Arguments
NOT NULL
Restricts values in the column to values that are not null.
NOT NULL PRIMARY KEY
PRIMARY KEY is a valid abbreviation of NOT NULL PRIMARY KEY.
Defines the column as the primary key for the table. There can be only one primary key for a table. A column with the NOT NULL PRIMARY KEY constraint cannot contain null or duplicate values. Other tables can name primary keys as foreign keys in their REFERENCES clauses.
If other tables name primary keys in their REFERENCES clauses, FairCom DB SQL restricts operations on the table containing the primary key:
The following example shows the creation of a primary key column on the table supplier.
CREATE TABLE supplier (
supp_no INTEGER NOT NULL PRIMARY KEY,
name CHAR (30),
status SMALLINT,
city CHAR (20)
) ;
NOT NULL UNIQUE
Defines the column as a unique key that cannot contain null or duplicate values. Columns with NOT NULL UNIQUE constraints defined for them are also called candidate keys.
Other tables can name unique keys in their REFERENCES clauses. If they do, FairCom DB SQL restricts operations on the table containing the unique key:
The following example creates a NOT NULL UNIQUE constraint to define the column ss_no as a unique key for the table employee:
CREATE TABLE employee (
empno INTEGER NOT NULL PRIMARY KEY,
ss_no INTEGER NOT NULL UNIQUE,
ename CHAR (19),
sal NUMERIC (10, 2),
deptno INTEGER NOT NULL
) ;
REFERENCES table_name [ (column_name) ]
Defines the column as a foreign key and specifies a matching primary or unique key in another table. The REFERENCES clause names the matching primary or unique key.
A foreign key and its matching primary or unique key specify a referential constraint: A value stored in the foreign key must either be null or be equal to some value in the matching unique or primary key.
You can omit the column_name argument if the table specified in the REFERENCES clause has a primary key and you want the primary key to be the matching key for the constraint.
The following example defines order_item.orditem_order_no as a foreign key that references the primary key orders.order_no.
CREATE TABLE orders (
order_no INTEGER NOT NULL PRIMARY KEY,
order_date DATE
) ;
CREATE TABLE order_item (
orditem_order_no INTEGER REFERENCES orders ( order_no ),
orditem_quantity INTEGER
) ;
Note that the second CREATE TABLE statement in the previous example could have omitted the column name order_no in the REFERENCES clause, since it refers to the primary key of table orders.
CHECK (search_condition)
Specifies a column-level check constraint. FairCom DB SQL restricts the form of the search condition. The search condition must not:
The following example creates a check constraint:
CREATE TABLE supplier (
supp_no INTEGER NOT NULL,
name CHAR (30),
status SMALLINT,
city CHAR (20) CHECK (supplier.city <> 'MOSCOW')
) ;
