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:

• The definitions of the table constraints are separated from the column definitions by commas.
• Table constraint definitions can include more than one column, and SQL evaluates the constraint based on the combination of values stored in all the columns.

Syntax

CONSTRAINT constraint_name 
    PRIMARY KEY ( column[ , ...] )
  | UNIQUE ( column[ , ...] )
  | FOREIGN KEY ( column[, ...] )
      REFERENCES [owner_name.]table_name[ ( column[ , ...] ) ]
   | CHECK ( search_condition )

Parameters

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:

• 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

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 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.

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:

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

Examples

In the following example, which shows creation of a table level primary key, note that its definition is separated from the column definitions by a comma:

CREATE TABLE SupplierItem (
     SuppNum   INTEGER NOT NULL,
     ItemNum   INTEGER NOT NULL,
     Quantity       INTEGER NOT NULL DEFAULT 0,
          PRIMARY KEY (SuppNum, ItemNum)) ;

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

CREATE TABLE OrderItem (
     OrderNum    INTEGER NOT NULL,
     ItemNum     INTEGER NOT NULL,
     Quantity         INTEGER NOT NULL,
     Price       INTEGER NOT NULL,
          UNIQUE (OrderNum, ItemNum),
          UNIQUE (Quantity, 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:

CREATE TABLE Courses (
     Instructor        CHAR (20) NOT NULL,
     CourseTitle   CHAR (30) NOT NULL,
     PRIMARY KEY (Instructor, CourseTitle));
CREATE TABLE StudentCourses (
     StudentID     INTEGER,
     Instructor        CHAR (20),
     CourseTitle   CHAR (30),
     FOREIGN KEY (Instructor, CourseTitle) REFERENCES Courses);

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:

(StudentCourses.Ieacher IS NULL 
     OR StudentCourses.CourseTitle IS NULL)
     OR EXISTS (SELECT * FROM StudentCourses WHERE
          (StudentCourses.Instructor = Courses.Instructor AND 
          StudentCourses.CourseTitle = Courses.CourseTitle));

In the following example, which 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 (
     SuppNum   INTEGER NOT NULL,
     Name      CHAR (30), 
     Status    SMALLINT CONSTRAINT StatusCheckCon
                   CHECK (Supplier.Status BETWEEN 1 AND 100 ),
     City      CHAR (20) CONSTRAINT CityCheckCon CHECK
                    (Supplier.City IN ('New York', 'Boston', 'Chicago')),
     CONSTRAINT SuppTabCheckCon CHECK (Supplier.City <> 'Chicago'
          OR Supplier.Status = 20)) ;