Preventing Duplicate Entries

Go Up to Using CREATE INDEX

No two rows can be alike when a UNIQUE index is specified for a column or set of columns. The system checks for duplicate values when the index is created, and each time a row is inserted or updated. InterBase automatically creates a UNIQUE index on a PRIMARY KEY column, forcing the values in that column to be unique identifiers for the row. Unique indexes only make sense when uniqueness is a characteristic of the data itself. For example, you would not define a unique index on a LAST_NAME column because there is a high probability for duplication. Conversely, a unique index is a good idea on a column containing a social security number.

To define an index that disallows duplicate entries, include the UNIQUE keyword in CREATE INDEX. The following statement creates a unique ascending index (PRODTYPEX) on the PRODUCT and PROJ_NAME columns of the PROJECT table:

CREATE UNIQUE INDEX PRODTYPEX ON PROJECT (PRODUCT, PROJ_NAME);

Tip: InterBase does not allow you to create a unique index on a column that already contains duplicate values. Before defining a UNIQUE index, use a SELECT statement to ensure there are no duplicate keys in the table. For example:

SELECT PRODUCT, PROJ_NAME FROM PROJECT
 GROUP BY PRODUCT, PROJ_NAME
 HAVING COUNT(*) > 1;