Constraints and Triggers

Constraints and Triggers

In this section we shall study those aspects of SQL that let us create "active" elements. An active element is an expression or statement that we write once, store in the database, and expect the element to execute at appropriate times. The time of action might be when a certain event occurs, such as an insertion into a particular relation, or it might be whenever the database changes so that a certain boolean-valued condition becomes true.

One of the serious problems faced by writers of applications that update the database is that the new information could be wrong in a variety of ways. For instance, there are often typographical or transcription errors in manually entered data. The most straightforward way to make sure that database modifications do not allow inappropriate tuples in relations is to write application programs so every insertion, deletion, and update command has associated with it the checks necessary to assure correctness. Unfortunately, the correctness requirements are often complex, and they are always repetitive; application programs must make the same tests after every modification.

Luckily, SQL provides a variety of techniques for expressing integrity constraints as part of the database schema. In this section we shall study the principal methods. First are key constraints, where an attribute or set of attributes is declared to be a key for a relation. Next, we consider a form of referential integrity, called "foreign-key constraints" which are the requirement that a value in an attribute or attributes of one relation (e.g., a presC# in Studio) must also appear as a value in an attribute or attributes of another relation (e.g., cert# of MovieExec).

Then, we consider constraints on attributes, tuples, and relations as a whole, and we cover interrelation constraints called "assertions." Finally, we discuss "triggers" which are a form of active element that is called into play on certain specified events,  such as insertion into a specific relation.

Keys and Foreign Keys

Perhaps the most important kind of constraint in a database is a declaration that a certain attribute or set of attributes forms a key for a relation. If a set of attributes S is a key for relation R, then any two tuples of R must disagree in at least one attribute in the set S. Note that this rule applies even to duplicate tuples; i.e., if R has a declared key, then R cannot have duplicates.

A key constraint, like many other constraints, is declared within the CREATE TABLE command of SQL. There are two similar ways to declare keys: using the keywords PRIMARY KEY or the keyword UNIQUE. However, a table may have only one primary key but any number of "unique" declarations.

SQL also uses the term "key" in connection with certain referential-integrity constraints. These constraints, called "foreign-key constraints," assert that a value appearing in one relation must also appear in the primary-key component(s) of another relation. We shall take up foreign-key constraints in "Declaring Foreign-Key Constraints".

Declaring Primary Keys

A relation may have only one primary key. There are two ways to declare a primary key in the CREATE TABLE statement that defines a stored relation.

1. We may declare one attribute to be a primary key when that attribute is listed in the relation schema.

2. We may add to the list of items declared in the schema (which so far have only been attributes) an additional declaration that says a particular attribute or set of attributes forms the primary key.

For method (1), we append the keywords PRIMARY KEY after the attribute and its type. For method (2), we introduce a new element in the list of attributes consisting of the keywords PRIMARY KEY and a parenthesized list of the attribute or attributes that form this key. Note that if the key consists of more than one attribute, we need to use method (2).

The effect of declaring a set of attributes S to be a primary key for relation R is twofold:

1. Two tuples in R cannot agree on all of the attributes in set S. Any attempt to insert or update a tuple that violates this rule causes the DBMS to reject the action that caused the violation.

2. Attributes in S are not allowed to have NULL as a value for their components.

Example 1 : Let us reconsider the schema for relation MovieStar from "Simple Table Declarations" Example 1. The primary key for this relation is name. Thus, we can add this

Making name the primary key

fact to the line declaring name. Figure 1 is a revision of "Simple Table Declarations" Figure 1 that reflects this change.

On the other hand, we can use a separate definition of the primary key. After line (5) of "Simple Table Declarations" Figure 1 we add a declaration of the primary key, and we have no need to declare it in line (2). The resulting schema declaration would look like Figure 2.

A separate declaration of the primary key

Note that in Example 1, the form of either Figure 1 or Figure 2 is acceptable, because the primary key is a single attribute. On the other hand, in a situation where the primary key has more than one attribute, we must use the style of Figure 2. For example, if we declare the schema for relation Movie, whose key is the pair of attributes title and year, we should add, after the list of attributes, the line:

PRIMARY KEY (title, year)