PL/SQL Unique Index

A PL/SQL Unique Index is a powerful database object used to ensure the uniqueness of values in one or more columns of a table. In Oracle databases, indexes play an important role in enhancing query performance by enabling quick retrieval of rows.

The unique index specifically enforces a rule that no two rows in a table can have the same values for the indexed column(s). In this article, we will explain the PL/SQL Unique Index in detail with syntax and examples.

PL/SQL Unique Index

By creating a unique index, the database automatically maintains data uniqueness, rejecting any attempts to insert or update records that violate this constraint. This feature makes unique indexes essential for maintaining data integrity, especially where duplicate entries must be prevented, such as email addresses or employee IDs.

Syntax:

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

key terms

• index_name: The name of the unique index.
  
• table_name: The name of the table, which is the unique index is being created.
  
• column1, column2, ...: The columns that can be unique index will be enforce the uniqueness on. We can specify that the more than one column for the composite uniqueness.

Example 1: Unique Index on email

In this example, we will create the unique index on email column to ensure that the no two users can have same email address. After creation of unique index, we will try to insert the statements into the users table

Query:

CREATE UNIQUE INDEX idx_users_email
ON users (email);

-- Insert a user with a unique email (valid)
INSERT INTO users (user_id, first_name, last_name, email)
VALUES (1, 'Alice', 'Smith', 'alice@example.com');

-- Insert another user with a different email (valid)
INSERT INTO users (user_id, first_name, last_name, email)
VALUES (2, 'Bob', 'Johnson', 'bob@example.com');

-- Insert a user with the same email (will fail)
INSERT INTO users (user_id, first_name, last_name, email)
VALUES (3, 'Charlie', 'Brown', 'alice@example.com');

Output:

ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.IDX_USERS_EMAIL) violated

Explanation:

• The first two insert statements will succeed because the email values are unique.
  
• The third insert statement is failed with the above error, because the email 'alice@example.com' already exist in the users table, so that it violates the unique index constraint.

Example 2: Unique Composite Index on first_name and email

In this example, we will create composite unique index on combination of the first_name and email. This ensures that the individual columns may have the duplicate values, the combination of both must be unique. After creating the index, let us try to insert the rows into the users table:

Query:

CREATE UNIQUE INDEX idx_users_firstname_email
ON users (first_name, email);

-- Insert a user with a unique combination of first_name and email (valid)
INSERT INTO users (user_id, first_name, last_name, email)
VALUES (1, 'John', 'Doe', 'john@example.com');

-- Insert another user with the same first_name but a different email (valid)
INSERT INTO users (user_id, first_name, last_name, email)
VALUES (2, 'John', 'Doe', 'john_doe@example.com');

-- Insert a user with a different first_name and the same email (valid)
INSERT INTO users (user_id, first_name, last_name, email)
VALUES (3, 'Jane', 'Doe', 'john@example.com');

-- Insert a user with the same first_name and email as an existing user (will fail)
INSERT INTO users (user_id, first_name, last_name, email)
VALUES (4, 'John', 'Smith', 'john@example.com');

Output:

ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.IDX_USERS_FIRSTNAME_EMAIL) violated

Explanation:

• In the above query, the first three insert statements will be succeeded because each has the unique combination of first_name and email.
  
• The fourth insert statement will be failed with the above error, because the combination of first_name = 'John' and email = 'john@example.com' already exists in users table and violating the composite unique index.

Example 3: Handling NULL Values with Unique Indexes

In Oracle, unique indexes allow multiple NULL values. However, non-NULL values must follow the uniqueness rule. After creation of unique index, let us try to insert the values into the users table using below given query.

Query:

CREATE UNIQUE INDEX users_email
ON users (email);

-- Insert a user with NULL email (valid)
INSERT INTO users (user_id, first_name, last_name, email)
VALUES (1, 'Alice', 'Smith', NULL);

-- Insert another user with NULL email (valid, as NULL is allowed)
INSERT INTO users (user_id, first_name, last_name, email)
VALUES (2, 'Bob', 'Johnson', NULL);

-- Insert a user with a non-NULL unique email (valid)
INSERT INTO users (user_id, first_name, last_name, email)
VALUES (3, 'Charlie', 'Brown', 'charlie@example.com');

-- Insert another user with the same non-NULL email (will fail)
INSERT INTO users (user_id, first_name, last_name, email)
VALUES (4, 'David', 'Clark', 'charlie@example.com');

Output:

ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.USERS_EMAIL) violated

Explanation:

• The first two inserts with NULL values succeed because Oracle allows multiple NULL values in columns with a unique index
  
• The third insert statement will be succeed, as 'charlie@example.com' is a unique the non-NULL value.
  
• The fourth insert statement will be failed with the above error, because the email 'charlie@example.com' is already present in table and the unique index prevent a insertion of the duplicate non-NULL values.

Conclusion

In conclusion, A PL/SQL Unique Index is the essential feature for the maintaining the data integrity in the Oracle database by ensuring that the specific columns or the combination of the columns contains the unique values.

By preventing duplicate entries, unique indexes help enforce the business rules like unique email addresses, product codes or other critical identifiers. They can created on the single columns or across the multiple columns.