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. The database returns a constraint violation error with an SQLCODE of -20116.
Column constraints are similar to table constraints, but their definitions are associated with a single column rather than the entire table.
Allows you to assign a name for the column constraint. This option facilitates making changes to the column definition. If you do not specify a constraint_name, the database assigns a name. These names can be long and unwieldy, and you must query system tables to retrieve the name.
NOT NULL
Restricts values in the column to values that are not null.
NOT NULL PRIMARY KEY
Defines the column as the primary key for the table. There can be at most one primary key for a table. A column with the NOT NULL PRIMARY KEY constraint should not contain null or duplicate values.
Other tables can name primary keys as foreign keys in their REFERENCES clauses. If they do, SQL restricts operations on the table containing the primary key in the following ways:
DROP TABLE statements that delete the table fail
DELETE and UPDATE statements that modify values in the column that match a foreign key's value also fail
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, SQL restricts operations on the table containing the unique key.
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.
CHECK ( search_condition )
Specifies a column‑level check constraint. SQL restricts the form of the search condition. The search condition must not:
Refer to any column other than the one with which it is defined
Contain aggregate functions, subqueries, or parameter references
Example
Creating a primary key
The following example shows the creation of a primary key column on the supplier table:
CREATE TABLE supplier (
supp_no INTEGER CONSTRAINT supp_key_con NOT NULL PRIMARY KEY,
name CHAR (30),
status SMALLINT,
city CHAR (20)
) ;
Creating a constraint to define the column as a unique key
The following example creates a NOT NULL UNIQUE constraint to define the column ss_no as a unique key for the employee table:
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
) ;
Defining a foreign key that references the primary key
The following example defines order_item.orditem_order_no as a foreign key that references the primary key orders.order_no:
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.
Creating a check constraint
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 <> 'BadApple')
) ;
If a column is defined with a UNIQUE column constraints, no error results if more than one row has a NULL value for the column.
DROP TABLE statements that delete the table fail