Release 10.1A: OpenEdge Data Management:
SQL 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:

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

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

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

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

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