Progress
SQL-92
Guide and Reference
Column Constraints
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 SQLCODE of -20116.
Column constraints are similar to table constraints, but their definitions are associated with a single column rather than the entire table.
SYNTAX
CONSTRAINT constraint_name
Allows you to assign a name that you choose to the column constraint. While this specification is optional, this 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. See the NOTE below.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:
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 in the following ways: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:
EXAMPLES
The following example shows the creation of a primary key column on the supplier table:
The following example creates a
NOT NULL UNIQUE
constraint to define the column ss_no as a unique key for the employee table.
The following example defines order_item.orditem_order_no as a foreign key that references the primary key orders.order_no:
NOTE: The secondCREATE TABLE
statement in the previous example could have omitted the column name order_no in theREFERENCES
clause, since it refers to the primary key of table orders.The following example creates a check constraint:
NOTE: 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. This is in compliance with the SQL-92 standard.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |