Latest Posts

Instead-Of Triggers

There is a useful feature of triggers that did not make the SQL-99 standard, but figured into the discussion of the standard and is supported by some commercial systems. This extension allows BEFORE or AFTER to be replaced by INSTEAD OF; the meaning is that when an event

Triggers in SQL

The SQL trigger statement gives the user a number of different options in the event, condition, and action parts. Here are the principal features. 1. The action may be executed either before or after the triggering event. 2. The action can refer to both old and/or new values of tuples that were inserted, deleted, or updated in the event that triggered the action.

Schema-Level Constraints and Triggers

The most powerful forms of active elements in SQL are not associated with specific tuples or components of tuples. These elements, called "triggers" and "assertions," are part of the database schema, on a par with the relations and views themselves.

Modification of Constraints

It is possible to add, modify, or delete constraints at any time. The way to express such modifications depends on whether the constraint involved is associated with an attribute, a table, or a database schema.

Tuple-Based CHECK Constraints

To declare a constraint on the tuples of a single table R, when we define that table with a CREATE TABLE statement we may add to the list of attributes and key or foreign-key declarations the keyword CHECK followed by a parenthesized condition. This condition can be anything that

Constraints on Attributes and Tuples

We have seen key constraints, which compel certain attributes to have distinct values among all the tuples of a relation, and we have seen foreign-key constraints, which enforce referential integrity between attributes of two relations. Now, we shall see a third important kind of

Deferring the Checking of Constraints

Suppose the situation of "Declaring Foreign-Key Constraints" Example 1, where presC# in Studio is a foreign key referencing cert# of MovieExec. Bill Clinton decides, after his national presidency, to found a movie studio, called Redlight Studios, of which he will naturally be the

Maintaining Referential Integrity

We have studied how to declare a foreign key, and we learned that this declaration implies that any set of values for the attributes of the foreign key, none of which are NULL, must also appear in the corresponding attribute(s) of the referenced relation. But how is this constraint

Declaring Foreign-Key Constraints

A second important kind of constraint on a database schema is that values for certain attributes must make sense. That is, an attribute like presC# of relation Studio is expected to refer to a particular movie executive. The implied "referential integrity" constraint is that if a studio's

Keys Declared With UNIQUE

One more way to declare a key is to use the keyword UNIQUE. This word can appear exactly where PRIMARY KEY can appear: either following an attribute and its type or as a separate item within a CREATE TABLE statement. The meaning of a UNIQUE declaration is nearly the