Documentation

SQL Reference Guide

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. FairCom DB 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 FairCom DB 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, FairCom DB 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

) ;

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

FairCom DB 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)

) ;

TOCIndex