SAND CDBMS supports the following column constraints: NOT NULL, UNIQUE, PRIMARY KEY, and FOREIGN KEY.
The NOT NULL constraint prevents null (unknown) values from being inserted or updated into the column on which the constraint is defined. A NOT NULL constraint can only be defined on a column when the column is created; the NOT NULL constraint cannot be subsequently added to an existing column. A NOT NULL constraint cannot be included in an ALTER TABLE...ADD COLUMN clause if the specified table already contains one or more rows of data, since the new column will initially be assigned null values, immediately violating the NOT NULL constraint.
The following SQL statement creates a table called license_type:
CREATE TABLE license_type (lic_type SMALLINT
CONSTRAINT lic_type_pk PRIMARY KEY,
CONSTRAINT lic_price_nn NOT NULL,
CONSTRAINT lic_active_nn NOT NULL);
Assume that this table will be used to store information about license types. We do not want the license price (lic_price) to be missing or unknown (that is, null), so the column is declared NOT NULL. Similarly, we want to ensure that there are no null values in the lic_active field (which indicates whether a license is active or obsolete), so this column is also defined as NOT NULL. Since the lic_type column is the primary key in this table, it is implicitly NOT NULL.
The UNIQUE constraint ensures that each value in the designated column is unique among all rows in the table. If an attempt is made to insert a value into a UNIQUE column and that value already exists in the column, the INSERT statement will fail. Similarly, if the outcome of an UPDATE statement is duplicate values in the UNIQUE column, the UPDATE will fail.
Note that a null value can be inserted into a column on which a UNIQUE constraint is defined. Since no two null values are equivalent, the UNIQUE constraint is not violated. To enforce unique non-null values in a column, the column should have both the UNIQUE and NOT NULL constraints defined on it.
Subsequent to the creation of the license_type table, it is decided that some additional information is required: the maximum number of users permitted under the license type. The following SQL statement adds the new column and defines it as UNIQUE:
ALTER TABLE license_type
ADD lic_num_users INT
CONSTRAINT lic_num_users_uk UNIQUE;
In this case, the UNIQUE constraint requires that the maximum number of users be different for each license type.
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 a primary key column; furthermore, only one primary key can be defined per table.
A PRIMARY KEY constraint cannot be included in an ALTER TABLE...ADD COLUMN clause if the specified table already contains one or more rows of data, since the new column will initially be assigned null values, immediately violating the constraint.
A column with a UNIQUE constraint already defined on it cannot subsequently be declared a primary key. The UNIQUE constraint on the column must first be dropped before setting the column as the primary key.
The following statement creates a table for storing information about work departments:
CREATE TABLE department (dept_no SMALLINT
CONSTRAINT dept_prim_key PRIMARY KEY,
In this example, the department number (dept_no) is designated as the primary key, ensuring that the internal code for each department is unique.
Referential integrity is supported in the form of FOREIGN KEY constraints. A FOREIGN KEY column constraint establishes a parent-child relationship between a column in one table and a corresponding column in another table. A FOREIGN KEY constraint references the unique key of another table, that is, a column on which a UNIQUE or PRIMARY KEY constraint is enforced.
The data type of the FOREIGN KEY column must exactly match the data type of the corresponding column 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. However, note that using a common domain can improve join performance.
The referential integrity rule requires that, for any value in the dependent column, there must exist a row in the parent table where the value of the dependent column equals the value of the corresponding column in the parent table. This is enforced as follows:
- 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.
- 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 the FOREIGN KEY column of each dependent record to be set to NULL before the corresponding parent records are updated or deleted. The FOREIGN KEY column involved cannot be defined as NOT NULL.
- SET DEFAULT: Causes the FOREIGN KEY column 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 a FOREIGN KEY column, regardless of whether the referenced column contains nulls.
The following statements create one table for car parts and another table for car part sales:
CREATE TABLE CarParts (cpid CHAR(6) PRIMARY KEY,
cpcode CHAR(4) NOT NULL UNIQUE,
CREATE TABLE CarPartSales (cpcode CHAR(4) REFERENCES CarParts (cpcode)
ON UPDATE CASCADE,
Here, the CarPartSales table establishes a FOREIGN KEY relation with the CarPart table on the respective cpcode fields. This means that whenever a value is inserted into the CarPartSales cpcode column, the same value must already exist in the CarParts cpcode column or else a FOREIGN KEY constraint violation will occur.
Since the cpcode column in the parent table is not the PRIMARY KEY, it must be specified explicitly in the REFERENCES clause of the CarPartSales creation statement.
The ON UPDATE CASCADE directive means that if a value in the parent key is updated, the same value in the FOREIGN KEY will be updated identically. For instance, if the CarParts cpcode column contains the value '1234', which is changed to '0000' via an UPDATE statement, each instance of '1234' in the CarPartSales cpcode column will likewise be changed to '0000'.
Note that, since the ON DELETE clause is omitted from the CarPartSales creation statement, the implied delete rule is ON DELETE RESTRICT.