Progress
SQL-92
Guide and Reference
Table Constraints
Specifies a constraint for a table that restricts the values that the table can store. INSERT, UPDATE, or DELETE statements that violate the constraint fail. SQL returns a constraint violation error.
Table constraints have syntax and behavior similar to Column Constraints. Note the following differences:
SYNTAX
CONSTRAINT constraint_name
Allows you to assign a name that you choose to the table constraint. While this specification is optional, this facilitates making changes to the table definition, since the name you specify is in your source
CREATE
TABLE
statement. 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 determine the name.PRIMARY KEY ( column [ , ... ] )
Defines the column list as the primary key for the table. There can be at most one primary key for a table.
All the columns that make up a table level primary key must be defined as
NOT
NULL
, or theCREATE
TABLE
statement fails. The combination of values in the columns that make up the primary key must be unique for each row in the table.Other tables can name primary keys in their
REFERENCES
clauses. If they do, SQL restricts operations on the table containing the primary key in the following ways:
UNIQUE ( column [ , ... ] )
Defines the column list as a unique, or candidate, key for the table. Unique key table-level constraints have the same rules as primary key table-level constraints, except that you can specify more than one
UNIQUE
table-level constraint in a table definition.FOREIGN KEY ( column [, ... ] ) REFERENCES [ owner_name.]table_name
[ ( column [ , ... ] ) ]Defines the first column list as as a foreign key and, in the
REFERENCES
clause, specifies a matching primary or unique key in another table.A foreign key and its matching primary or unique key specify a referential constraint. The combination of values stored in the columns that make up a foreign key must either:
- Have at least one of the column values be null
- Be equal to some corresponding combination of values in the matching unique or primary key
You can omit the column list in the
REFERENCES
clause if the table specified in theREFERENCES
clause has a primary key and you want the primary key to be the matching key for the constraint.CHECK (search_condition)
Specifies a table level check constraint. The syntax for table level and column level check constraints is identical. Table level check constraints must be separated by commas from surrounding column definitions.
SQL restricts the form of the search condition. The search condition must not:
EXAMPLES
The following example shows creation of a table level primary key. Note that its definition is separated from the column definitions by a comma:
The following example shows how to create a table with two
UNIQUE
table level constraints:
The following example defines the combination of columns student_courses.teacher and student_courses.course_title as a foreign key that references the primary key of the courses table. Note that this REFERENCES clause does not specify column names because the foreign key refers to the primary key of the courses table:
SQL evaluates the referential constraint to see if it satisfies the following search condition:
NOTE: INSERT, UPDATE, or DELETE statements that cause the search condition to be false violate the constraint, fail, and generate an error.The following example creates a table with two column level check constraints and one table level check constraint. Each constraint is defined with a name.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |