Column-Level Constraints and Table-Level Constraints
Constraints can be specified for individual columns as part of the column specification (column-level constraints) or for groups of columns as part of the table definition (table-level constraints).
The constraint has the following syntax:
[CONSTRAINT constraint_name] constraint
constraint_name
Defines a name for the constraint. If the name is omitted, the DBMS Server assigns one. The constraint name is used when dropping the constraint using the ALTER TABLE statement.
Note: We recommend defining a name when creating a constraint; otherwise system catalogs must be queried to determine the system-defined name.
constraint
Is either a column-level constraint (column_constraint) or table-level constraint (table_constraint).
column_constraint is one or more of the following:
UNIQUE [WITH constraint_with_clause]
PRIMARY KEY [WITH constraint_with_clause]
REFERENCES [schema.]table_name[(column_name)]
[WITH constraint_with_clause]
table_constraint is one or more of the following:
UNIQUE (column_name {, column_name}) [WITH constraint_with_clause]
PRIMARY KEY (column_name {, column_name}) [WITH constraint_with_clause]
FOREIGN KEY (column_name {, column_name})
REFERENCES [schema.]table_name [(column_name {, column_name})]
[WITH constraint_with_clause]
Examples
Here is an example of column-level constraints:
CREATE TABLE mytable(name CHAR(10) NOT NULL,
id INTEGER REFERENCES idtable(id),
age INTEGER CHECK (age > 0));
Note: Multiple column constraints are separated by a space.
Here is an example of table-level constraints:
CREATE TABLE yourtable(firstname CHAR(20) NOT NULL,
lastname CHAR(20) NOT NULL,
UNIQUE(firstname, lastname));