The tables in a database define its basic structure, and in most early commercial SQL products, the table definitions were the only specification of database structure. With the advent of primary key/foreign key support in DB2 and in the SQL2 standard, the definition of database structure was expanded to include the relationships among the tables in a database. More recently, through the SQL2 standard and the evolution of commercial products, the definition of database structure has expanded to include a new area—database constraints that restrict the data that can be entered into the database. The types of constraints, and the role that they play in maintaining database integrity, are described in Chapter 11.
Four types of database constraints (uniqueness constraints, primary and foreign key constraints, and check constraints) are closely associated with a single database table. They are specified as part of the CREATE TABLE statement and can be modified or dropped using the ALTER TABLE statement. The other two types of database integrity constraints, assertions and domains, are created as stand-alone objects within a database, independent of any individual table definition.
1. Assertions
An assertion is a database constraint that restricts the contents of the database as a whole. Like a check constraint, an assertion is specified as a search condition. But unlike a check constraint, the search condition in an assertion can restrict the contents of multiple tables and the data relationships among them. For that reason, an assertion is specified as part of the overall database definition, via a SQL2 CREATE ASSERTION statement. Suppose you wanted to restrict the contents of the sample database so that the total orders for any given customer may not exceed that customer’s credit limit. You can implement that restriction with the statement:
CREATE ASSERTION CREDLIMIT
CHECK ((CUSTOMERS.CUST_NUM = ORDERS.CUST) AND
(SUM (AMOUNT) <= CREDIT_LIMIT))
With the assertion named CREDLIMIT as part of the database definition, the DBMS is required to check that the assertion remains true each time a SQL statement attempts to modify the CUSTOMER or ORDERS tables. If you later determine that the assertion is no longer needed, you can drop it using the DROP ASSERTION statement:
DROP ASSERTION CREDLIMIT
There is no SQL2 ALTER ASSERTION statement. To change an assertion definition, you must drop the old definition and then specify the new one with a new CREATE ASSERTION statement.
2. Domains
The SQL2 standard implements the formal concept of a domain as a part of a database definition. As described in Chapter 11, a domain is a named collection of data values that effectively functions as an additional data type, for use in database definitions. A domain is created with a CREATE DOMAIN statement. Once created, the domain can be referenced as if it were a data type within a column definition. Here is a CREATE DOMAIN statement to define a domain named VALID_EMPL_IDS, which consists of valid employee identification numbers in the sample database. These numbers are three-digit integers in the range 101 to 999, inclusive:
CREATE DOMAIN VALID_EMPL_IDS INTEGER
CHECK (VALUE BETWEEN 101 AND 199)
If a domain is no longer needed, you can drop it using one of the forms of the SQL2 DROP DOMAIN statement:
DROP DOMAIN VALID_EMPL_IDS CASCADE
DROP DOMAIN VALID_EMPL_IDS RESTRICT
The CASCADE and RESTRICT drop rules operate just as they do for dropped columns.
If CASCADE is specified, any column defined in terms of the dropped domain will also be automatically dropped from the database. If RESTRICT is specified, the attempt to drop the domain will fail if any column definitions are based on it. You must first drop or alter the column definitions so that they no longer depend on the domain before dropping it. This provides an extra margin of safety against accidentally dropping columns (and more importantly, the data that they contain).
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.