UNIQUE Key

The UNIQUE key constraint in Oracle is used to ensure that all values in a column (or a combination of columns) are distinct across all rows in a table. While it allows NULL values (with certain exceptions), it guarantees that no two rows have the same non-null value in the specified column(s).

Here's a detailed breakdown of the UNIQUE constraint in Oracle:

1. What is the UNIQUE Key Constraint?

The UNIQUE constraint ensures that each value in a column (or a group of columns) must be unique across all rows in a table. This constraint allows NULLs, but different rows can have NULLs as long as the non-NULL values are distinct.

• Purpose: It enforces uniqueness, preventing duplicate entries in a column or combination of columns.
• Behavior with NULLs:
• In Oracle, a UNIQUE constraint allows multiple NULL values in the column because NULL is not considered a value in the same way as regular data (i.e., NULL is not equal to another NULL).
• Therefore, if a column with a UNIQUE constraint contains multiple NULLs, Oracle will allow this.

 

2. Syntax for Defining the UNIQUE Constraint

Defining UNIQUE during Table Creation:

When creating a table, you can define a UNIQUE constraint on one or more columns.

• Single Column:

CREATE TABLE employees (

    employee_id NUMBER PRIMARY KEY,

    email VARCHAR2(100) UNIQUE

);

• Multiple Columns (Composite Unique Constraint): You can also create a UNIQUE constraint on a combination of columns to ensure that the combination of values in those columns is unique across all rows.

CREATE TABLE employees (

    first_name VARCHAR2(50),

    last_name VARCHAR2(50),

    hire_date DATE,

    CONSTRAINT unique_employee_name UNIQUE (first_name, last_name)

);

Adding UNIQUE Constraint to an Existing Table:

If the table already exists and you want to add a UNIQUE constraint to a column (or combination of columns), you can use the ALTER TABLE statement.

• Single Column:

ALTER TABLE employees

ADD CONSTRAINT unique_email UNIQUE (email);

• Multiple Columns:

ALTER TABLE employees

ADD CONSTRAINT unique_employee_name UNIQUE (first_name, last_name);

 

3. Behavior of the UNIQUE Constraint

• Duplicate Values: The UNIQUE constraint prevents duplicate non-NULL values in a column or combination of columns. If you try to insert a duplicate non-NULL value, Oracle will raise a constraint violation error.
• Example:

INSERT INTO employees (employee_id, email) VALUES (1, 'john.doe@example.com');

INSERT INTO employees (employee_id, email) VALUES (2, 'john.doe@example.com');  -- This will fail!

• NULL Values: You can insert multiple NULL values in a column with a UNIQUE constraint because NULL is not considered a duplicate. The UNIQUE constraint only enforces uniqueness among non-NULL values.
• Example:

INSERT INTO employees (employee_id, email) VALUES (1, 'john.doe@example.com');

INSERT INTO employees (employee_id, email) VALUES (2, NULL);

INSERT INTO employees (employee_id, email) VALUES (3, NULL);  -- This is allowed!

 

4. Composite UNIQUE Constraints

A composite UNIQUE constraint involves more than one column. The combination of values in these columns must be unique, though individual columns may have duplicate values.

• Example:

CREATE TABLE orders (

    order_id NUMBER PRIMARY KEY,

    customer_id NUMBER,

    product_id NUMBER,

    order_date DATE,

    CONSTRAINT unique_customer_product UNIQUE (customer_id, product_id)

);

In this example, the customer_id and product_id combination must be unique. However, the customer_id or product_id individually may have duplicates across rows.

 

5. Indexing with the UNIQUE Constraint

• Automatic Index Creation: When you define a UNIQUE constraint on a column (or combination of columns), Oracle automatically creates a unique index on the column(s) involved in the constraint. This index improves query performance when checking for uniqueness.

Example:

CREATE TABLE employees (

    employee_id NUMBER PRIMARY KEY,

    email VARCHAR2(100) UNIQUE  -- Oracle automatically creates an index on the 'email' column.

);

• Index Behavior: The index created by the UNIQUE constraint is used to enforce uniqueness and speed up searches, inserts, and updates involving the UNIQUE column(s).

 

6. Dropping the UNIQUE Constraint

If you no longer need the UNIQUE constraint, you can drop it using the ALTER TABLE statement.

• Single Column:

ALTER TABLE employees

DROP CONSTRAINT unique_email;

• Multiple Columns:

ALTER TABLE employees

DROP CONSTRAINT unique_employee_name;

 

7. Differences Between UNIQUE and PRIMARY KEY Constraints

• PRIMARY KEY:
• Ensures that the values in the column (or columns) are unique and not NULL.
• A table can have only one PRIMARY KEY constraint.
• A PRIMARY KEY implicitly creates a unique index.
• UNIQUE Key:
• Ensures that the values in the column (or columns) are unique, but allows NULL values.
• A table can have multiple UNIQUE constraints.
• A UNIQUE constraint creates a unique index.

 

8. Use Cases for UNIQUE Constraint

• Ensuring Email Uniqueness: In a user table, the email column should have a UNIQUE constraint to ensure that no two users can have the same email address.

CREATE TABLE users (

    user_id NUMBER PRIMARY KEY,

    email VARCHAR2(100) UNIQUE

);

• Ensuring Unique Combinations: You may want to ensure that combinations of certain fields, like a customer and product pair, are unique in an order system.

CREATE TABLE orders (

    order_id NUMBER PRIMARY KEY,

    customer_id NUMBER,

    product_id NUMBER,

    CONSTRAINT unique_customer_product UNIQUE (customer_id, product_id)

);

 

9. Performance Considerations

• Insert Performance: While the UNIQUE constraint can slightly degrade the performance of inserts, it significantly improves query performance for searching or looking up distinct values.
• Indexing Overhead: The automatic index created by the UNIQUE constraint has some overhead, especially when managing large datasets. However, this overhead is usually outweighed by the performance improvements in queries and the enforcement of data integrity.

 

10. Advantages of the UNIQUE Constraint

• Data Integrity: It ensures that no two rows have the same value in the specified column(s), thereby maintaining data integrity.
• Enforcement of Business Rules: It can be used to enforce business rules such as ensuring that each product code, email, or customer ID is unique.
• Improved Query Performance: The index created by the UNIQUE constraint speeds up searches, lookups, and joins.

 

11. Disadvantages or Considerations

• Allowing Multiple NULLs: Although NULL values are allowed under the UNIQUE constraint, this may not be suitable for all scenarios where NULLs are not considered valid.
• Performance Impact: The creation of indexes for UNIQUE constraints can affect the performance of INSERT, UPDATE, and DELETE operations, especially in large tables.