SAND CDBMS SQL Reference Guide
Constraints

Table Constraints

SAND CDBMS supports the following table constraints: UNIQUE, PRIMARY KEY, and FOREIGN KEY.

UNIQUE

The UNIQUE table constraint ensures that the combination of values in the designated columns is unique among all rows in the table. If an attempt is made to insert a duplicate value set into the columns participating in a UNIQUE constraint, the INSERT statement will fail. Similarly, if an UPDATE statement produces a duplicate value set in the UNIQUE columns, the UPDATE will fail.

A UNIQUE constraint can have at most 64 participating columns.

Example

Assume that a table called inventory already exists in the database, and that three of the columns in the table are called model, type, and qoh. The following ALTER TABLE statement adds a UNIQUE constraint to that table:

ALTER TABLE inventory
  ADD UNIQUE (model, type, qoh);

This added constraint guarantees that the combined values of columns model, type, and qoh will remain unique for each row in the inventory table.

PRIMARY KEY

The purpose of the PRIMARY KEY constraint is to enforce entity integrity — that is, to identify each row of a table uniquely. In this respect, it is similar to the UNIQUE key, except that null values are not permitted in any of the columns included in the PRIMARY KEY constraint; furthermore, there can only be one primary key defined per table.

Attempting to add a PRIMARY KEY constraint to a table, through the ALTER TABLE...ADD CONSTRAINT command, will fail if any of the columns participating in the constraint contain a null value.

A column that is part of a UNIQUE constraint cannot subsequently be included in a primary key. The UNIQUE constraint must first be dropped before the column can participate in the primary key.

A PRIMARY KEY constraint can have at most 64 participating columns.

Example

The following example is the table constraint equivalent to the PRIMARY KEY column constraint example:

CREATE TABLE department (dept_no SMALLINT,
                         bldg_code CHAR(4),
                         CONSTRAINT dept_prim_key PRIMARY KEY (dept_no));

If there is only one column in the primary key, the constraint can be defined as either a column constraint or as a table constraint with a single column reference in the column list. The difference is the PRIMARY KEY column constraint can only be set when the table is created or when a new column (the primary key) is being added to the table, whereas a table constraint can be defined at any time.

FOREIGN KEY

A FOREIGN KEY constraint establishes a parent-child relationship between one or more columns in a table and one or more columns in another table. A FOREIGN KEY constraint references the unique key of another table, that is, one or more columns on which a UNIQUE or PRIMARY KEY constraint is enforced.

The data type(s) of the FOREIGN KEY column(s) must exactly match the data type(s) of the corresponding column(s) in the parent (referenced) table. The domains of the respective columns can differ, as long as the domains are defined on exactly the same data type(s).

The referential integrity rule requires that, for any value in the dependent column(s), there must exist a row in the parent table where the value of the dependent column(s) equal(s) the value of the corresponding column(s) in the primary key of the parent table. This is enforced as follows:

1. When an update or insert is performed on the dependent table, the set of values placed in the referencing columns must match a set of values that exists in the parent table.
2. If an attempt is made to delete or update a row of the parent table that contains values matching those in a dependent table, the system uses the ON DELETE or ON UPDATE clause to determine the delete or update rule to be used. The options are:
   • RESTRICT (default): Prevents the records from being updated or deleted from the parent table if dependents exist.
   • CASCADE: Updates or deletes the dependent records from the current table and then updates or deletes the parent records.
   • SET NULL: Causes each column in the FOREIGN KEY of each dependent record to be set to NULL before the corresponding parent records are updated or deleted. The FOREIGN KEY columns involved cannot be defined as NOT NULL.
   • SET DEFAULT: Causes each column in the FOREIGN KEY of each dependent record to be set to its DEFAULT value before the corresponding parent records are updated or deleted.

Null values can be stored in FOREIGN KEY columns, regardless of whether the parent key contains nulls, but only when all fields in the FOREIGN KEY are set to null in the inserted row.

Example

The following SQL statements create a table called CatalogItems and another called WarehouseInventory:

CREATE TABLE CatalogItems (prod_code CHAR(8),
                           region_code CHAR(2),
                           prod_desc CHAR(1024),
              PRIMARY KEY (prod_code, region_code));

CREATE TABLE WarehouseInventory (whcode CHAR(5),
                                 pcode CHAR(8),
                                 rcode CHAR(2),
                                 pqty INT,
                    FOREIGN KEY (pcode, rcode)
                    REFERENCES CatalogItems
                      ON UPDATE SET NULL
                      ON DELETE RESTRICT);

In this example, WarehouseInventory’s FOREIGN KEY (composed of pcode and rcode together) references CatalogItems’ PRIMARY KEY (prod_code and region_code). If, during an INSERT or UPDATE operation, there is an attempt to insert into the WarehouseInventory table a row whose values for pcode and rcode do not correspond exactly to the values for prod_code and region_code in an existing row in the CatalogItems table, a FOREIGN KEY constraint error is returned.

The FOREIGN KEY definition includes instructions on how to handle updates and deletes on referenced columns. The ON UPDATE SET NULL clause specifies that if a value in a referenced column is updated, the same value(s) in the FOREIGN KEY column are set to null. For instance, if the prod_code value ' 12345678' is updated to '00000000', every instance of the same value '12345678' in pcode is set to null.

The ON DELETE RESTRICT clause specifies that the deletion of a record from the parent table will be prevented if the record contains a value in one of the referenced columns that is also contained in the corresponding FOREIGN KEY column. For instance, if a CatalogItems record contains the values '77777777' for prod_code and 'US' for region_code, the record cannot be deleted if in the WarehouseInventory table either pcode contains '77777777' or rcode contains 'US' (or both).

Note that in the WarehouseInventory creation statement, the REFERENCES clause does not explicitly reference any columns in the parent table. In this case it is unnecessary to specify the columns, since the FOREIGN KEY is based on the PRIMARY KEY of the parent table.

Note also that the ON DELETE option could have been omitted from the WarehouseInventory creation statement, since it specifies the default behavior (RESTRICT).