Keys Declared With UNIQUE

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 same as the meaning of a PRIMARY KEY declaration. There are two distinctions, though:

1. We may have any number of UNIQUE declarations for a table, but only one primary key.

2. While PRIMARY KEY prevents NULL's in the attributes of the key, UNIQUE allows them. Furthermore, the rule that two tuples may not agree in all of a set of attributes declared UNIQUE may be violated if one or more of the components involved have NULL as a value. Actually, it is even allowed for both tuples to have NULL in all corresponding attributes of the UNIQUE key.

The implementor of a DBMS has the option to make additional distinctions. For example, a database vendor might always place an index on a key declared to be a primary key (even if that key consisted of more than one attribute), but require the user to call for an index explicitly on other attributes. Instead, a table might always be kept sorted on its primary key, if it had one.

Example 1 : Line (2) of "Constraints and Triggers" Figure 1 could have been written

    2)   name CHAR(30) UNIQUE,

We could also change line (3) to

    3)  address VARCHAR(255) UNIQUE,

if we felt that two movie stars could not have the same address (a doubtful assumption). Likewise, we could change line (6) of "Constraints and Triggers" Figure 2 to

    6)  UNIQUE (name)

should we choose.

Enforcing Key Constraints

Remember our discussion of indexes in "Default Values / Indexes", where we learned that although they are not part of any SQL standard, each SQL implementation has a way of creating indexes as part of the database schema definition. It is usual to build an index on the primary key, in order to support the common type of query that specifies a value for the primary key. We may also want to build indexes on other attributes declared to be UNIQUE.

Then, when the WHERE clause of the query contains a condition that equates a key to a particular value - for example name = 'Audrey Hepburn' in the case of the MovieStar relation of "Constraints and Triggers" Example 1 - the matching tuple will be found very quickly, without a search through all the tuples of the relation.

Many SQL implementations offer an index-creation statement using the keyword UNIQUE that declares an attribute to be a key at the same time it creates an index on that attribute. For instance, the statement

CREATE UNIQUE INDEX Year Index ON Movie(year);

would have the same effect as the example index-creation statement in "Default Values / Indexes", but it would also declare a uniqueness constraint on attribute year of the relation Movie (not a reasonable assumption).

Let us assume for a moment how an SQL system would enforce a key constraint. In principle, the constraint must be checked every time we try to change the database. On the other hand, it should be clear that the only time a key constraint for a relation R can become violated is when R is customized. Actually, a deletion from R cannot cause a violation; only an insertion or update can. Therefore, it is normal practice for the SQL system to check a key constraint only when an insertion or update to that relation takes place.

An index on the attribute(s) declared to be keys is vital if the SQL system is to enforce a key constraint efficiently. If the index is available, then whenever we insert a tuple into the relation or update a key attribute in some tuple, we use the index to check that there is not already a tuple with the same value in the attribute(s) declared to be a key. If so, the system must stop the modification from taking place.

If there is no index on the key attribute(s), it is still possible to enforce a key constraint. Sorting the relation by key-value helps us search. However, in the absence of any aid to searching, the system must inspect the entire relation, looking for a tuple with the given key value. That process is very time-consuming and would render database modification of large relations virtually impossible.