UNIQUE

As well as primary-key constraints, you can add unique key constraints to your tables with UNIQUE (<column>) clauses in CREATE TABLE and ALTER TABLE, or with the nonstandard SQL-extension CREATE UNIQUE INDEX ... (<column>) statement. All DBMSs allow nonprimary unique keys, but IBM, Informix, Ingres, InterBase, Microsoft, and Sybase have an odd restrictionthey don't allow two NULL values in a column if that column is defined with a UNIQUE constraint. (In fact, neither IBM, Ingres, nor InterBase allow any NULLs in a column defined with a UNIQUE constraint, although IBM does allow one NULL in a UNIQUE INDEX key.) It's as if these DBMSs think that "NULL = NULL"which, as students of NULL theory know, is not the case. One should be able to have NULLs in unique keys as well as in foreign keys because values may be unknown initiallyor they may be unspecifiable initially, as in cyclic references.

Even if all values are guaranteed in advance to be unique, your DBMS will check for uniqueness anyway (unless you turn the constraints off, as we'll discuss later in this chapter). To check for uniqueness, the DBMS has to look up the value in a B-tree and then update the B-tree. Happily, and contrary to the case of just a few years ago, it is now possible to execute this SQL statement with many DBMSs:

 UPDATE Table1    SET unique_column = unique_column + 1 

Portability

Once there was a problemand it's still a problem for Informix, Ingres, InterBase, and MySQLthat such updates would fail if two rows in Table1 contain sequential values.

For example, assume Table1 contains these six rows:

	Table1.unique_column
Row #1	1
Row #2	2
Row #3	3
Row #4	4
Row #5	5
Row #6	6

To execute the UPDATE statement, the DBMS updates the first row of Table1 , causing unique_column to equal 2 . Then it tries to update the unique index, which already has a 2 in it (from Row #2). In the past, and with some DBMSs even now, the DBMS would fail at this point. The trick is that the uniqueness test must be made at the end of statement, not at the end of processing for each row, even though that's less convenient for the DBMS. This also applies for PRIMARY KEY constraints, because a PRIMARY KEY constraint is just a type of UNIQUE constraint. In practice, though, a primary key is nonvolatile so the situation should not arise.

Rule-based optimizers will give some preference to unique indexes, knowing that they have guaranteed selectivity.

The Bottom Line: UNIQUE Constraints

If it's unique, define it as UNIQUE so the optimizer will know.