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  
    PRIMARY KEY ( column [ , ... ] ) 
  | UNIQUE ( column [ , ... ] ) 
  | FOREIGN KEY ( column [, ... ] ) 
      REFERENCES [ owner_name.]table_name [ ( column [ , ... ] ) ] 
  | CHECK ( search_condition ) 

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 the CREATE 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:

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:

CREATE TABLE supplier_item ( 
     supp_no   INTEGER NOT NULL, 
     item_no   INTEGER NOT NULL, 
     qty       INTEGER NOT NULL DEFAULT 0, 
          PRIMARY KEY (supp_no, item_no) 
) ; 

The following example shows how to create a table with two UNIQUE table level constraints:

CREATE TABLE order_item ( 
     order_no    INTEGER NOT NULL, 
     item_no     INTEGER NOT NULL, 
     qty         INTEGER NOT NULL, 
     price       MONEY NOT NULL, 
          UNIQUE (order_no, item_no), 
          UNIQUE (qty, price) 
) ; 

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:

CREATE TABLE courses ( 
     teacher        CHAR (20) NOT NULL, 
     course_title   CHAR (30) NOT NULL, 
     PRIMARY KEY (teacher, course_title) 
) ; 
CREATE TABLE student_courses ( 
     student_id     INTEGER, 
     teacher        CHAR (20), 
     course_title   CHAR (30), 
     FOREIGN KEY (teacher, course_title) REFERENCES courses 
) ; 

SQL evaluates the referential constraint to see if it satisfies the following search condition:

(student_courses.teacher IS NULL  
     OR student_courses.course_title IS NULL) 
     OR EXISTS (SELECT * FROM student_courses WHERE 
          (student_courses.teacher = courses.teacher AND  
          student_courses.course_title = courses.course_title) 
          ) 

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.

CREATE TABLE supplier ( 
     supp_no   INTEGER NOT NULL, 
     name      CHAR (30),  
     status    SMALLINT CONSTRAINT status_check_con 
                   CHECK ( supplier.status BETWEEN 1 AND 100 ), 
     city      CHAR (20) CONSTRAINT city_check_con CHECK 
                    ( supplier.city IN ('NEW YORK', 'BOSTON', 'CHICAGO')), 
     CONSTRAINT supp_tab_check_con CHECK (supplier.city <> 'CHICAGO' 
          OR supplier.status = 20) 
) ; 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095