Previous Topic

Next Topic

Table Constraints

Description

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. c-treeACE SQL returns a Constraint violation error.

Table constraints have syntax and behavior similar to column constraints. Note the following differences:

  • The syntax for table constraints is separated from column definitions by commas.
  • Table constraints must follow the definition of columns they refer to.
  • Table constraint definitions can include more than one column and c-treeACE SQL evaluates the constraint based on the combination of values stored in all the columns.

Syntax

table_constraint ::
 PRIMARY KEY ( column [, … ] )
 | UNIQUE ( column [, … ] )
 | FOREIGN KEY ( column [, … ] )
   REFERENCES [ owner_name. ] table_name [ ( column [, … ] ) ]
 | CHECK ( search_condition )

Arguments

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, c-treeACE SQL restricts operations on the table containing the primary key:

  • DROP TABLE statements that delete the table fail
  • DELETE and UPDATE statements that modify values in the combination of columns that match a foreign key’s value also fail

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)
) ;

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.

The following example shows creation of 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)
) ;

FOREIGN KEY … REFERENCES

Defines the first column list 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 the REFERENCES clause has a primary key and you want the primary key to be the matching key for the constraint.

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 table courses. Note that the 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
) ;

c-treeACE 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)
        )

INSERT, UPDATE or DELETE statements that cause the search condition to be false violate the constraint, fail, and generate an error.

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.

c-treeACE SQL restricts the form of the search condition. The search condition must not:

  • Refer to any column other than columns that precede it in the table definition
  • Contain aggregate functions, subqueries, or parameter references

The following example creates a table with two column-level check constraints and one table-level check constraint:

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