5.6 How do I…Enforce uniqueness via an index?

Problem

My business rules require that each record in a table have unique values in certain columns. I want to enforce this rule by making a unique index. I know that this will also improve performance when querying data. How do I enforce uniqueness via an index?

Technique

Use the UNIQUE clause of the CREATE INDEX command to create unique indexes. A unique index will ensure that no two records will have the same values in the columns that comprise the index. Any attempt to add a new record that contains duplicate values will be rejected. Also, any attempt to alter the data contained in the indexed columns will be rejected.

Steps

1. Connect to SQL*Plus as the WAITE user account. If you have not created the EMPLOYEE05 table, run the CHP5_1.SQL script to create it. The description of the EMPLOYEE05 table appears in Figure 5.13.

The decision of which columns to make unique, if any, will depend on the business rules of your application. For example, it may not make sense to make the STATE column unique, as there may be more than one employee from a given state. It may also not make sense to make the EMPLOYEE_NAME column unique, as there may be two employees with the same name. Also, there may be two different possible employee names for a Social Security number (if you store nicknames, for example). In this How-To, we will create a unique index on the combination of EMPLOYEE_NAME and SSN columns for the EMPLOYEE05 table.

2. Create the unique index on the EMPLOYEE_NAME and SSN columns by running CHP5_11.SQL.

SQL> GET CHP5_11.sql

1 CREATE UNIQUE INDEX name_ssn_index

2 ON EMPLOYEE05 (EMPLOYEE_NAME, SSN)

SQL> /

Index created.

3. At this point, Oracle will automatically reject any records that duplicate the combination of EMPLOYEE_NAME and SSN. Insert a sample record into the EMPLOYEE05 table by running CHP5_12.SQL.

SQL> GET CHP5_12.sql

1 INSERT INTO EMPLOYEE05

2 VALUES (‘AARON LIPMAN’,111111111,100000,

3 ‘CA’,’Photocopy Boy’, sysdate)

SQL> /

1 row created.

4. At this point, there will be a new record in the table. If, however, you try to insert another record containing the same EMPLOYEE_NAME and SSN, Oracle will reject the statement and return an error. Run CHP5_13.SQL to create such a record.

SQL> GET CHP5_13.sql

1 INSERT INTO EMPLOYEE05

2 VALUES (‘AARON LIPMAN’,111111111,25000,

3 ‘MA’,’Student Intern’, sysdate)

SQL> /

INSERT INTO EMPLOYEE05

* ERROR at line 1:

ORA-00001: unique constraint (WAITE.NAME_SSN_INDEX) violated

You can see that Oracle automatically rejected the insert attempt.

How It Works

Step 1 creates the EMPLOYEE05 table used in this How-To. Step 2 creates the unique index on the EMPLOYEE_NAME and SSN columns together on the EMPLOYEE05 table. Step 3 inserts a sample record into the table. Step 4 tries to enter a record with duplicate EMPLOYEE_NAME and SSN values. Oracle rejects this attempt due to the existence of the unique key NAME_SSN_INDEX.

Comments

Unique keys are automatically created when a primary key constraint or a unique key constraint are created on the table. How-To 6.1 describes how to add such a primary key constraint.