SQL UNIQUE Constraint

The UNIQUE constraint in SQL ensures that all the values in a specified column or a combination of columns are distinct. It prevents duplicate values from being inserted into the column(s) where it is applied.

The UNIQUE constraint is crucial for maintaining data integrity, particularly when specific data must remain unique within a table, such as email addresses, usernames, or product codes.

Features of UNIQUE Constraint

Prevents Duplicate Values: Ensures all values in the constrained column(s) are unique.
Allows One NULL Value: Unlike the PRIMARY KEY constraint, a UNIQUE constraint permits a single NULL value.
Can Be Applied to Multiple Columns: When applied to multiple columns, the combination of values across these columns must be unique.

Syntax

Defining UNIQUE Constraint During Table Creation

For a single column:

CREATE TABLE table_name (
    column_name data_type UNIQUE,
    other_column data_type
);

For multiple columns:

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    CONSTRAINT unique_constraint_name UNIQUE (column1, column2)
);

Adding UNIQUE Constraint to an Existing Table

For a single column:

ALTER TABLE table_name
ADD CONSTRAINT unique_constraint_name UNIQUE (column_name);

For multiple columns:

ALTER TABLE table_name
ADD CONSTRAINT unique_constraint_name UNIQUE (column1, column2);

Dropping a UNIQUE Constraint

ALTER TABLE table_name
DROP CONSTRAINT unique_constraint_name;

Examples

1. UNIQUE Constraint on a Single Column

Create a table where the email column must be unique.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

This ensures that no two users can have the same email address.

2. UNIQUE Constraint on Multiple Columns

Create a table where the combination of first_name and last_name must be unique.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    CONSTRAINT unique_name UNIQUE (first_name, last_name)
);

This ensures no two employees can have the same first and last name combination.

3. Adding UNIQUE Constraint to an Existing Table

Add a UNIQUE constraint to the username column of an existing users table.

ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);

4. Dropping a UNIQUE Constraint

Remove the UNIQUE constraint from the username column.

ALTER TABLE users
DROP CONSTRAINT unique_username;

Key Differences Between UNIQUE and PRIMARY KEY

Feature	UNIQUE Constraint	PRIMARY KEY
Enforces Uniqueness	Yes	Yes
Allows NULL Values	Yes (one NULL value allowed)	No
Multiple Constraints	Can have multiple `UNIQUE` constraints in a table	Only one `PRIMARY KEY` per table
Index Type	Creates a non-clustered index	Creates a clustered index

Use Cases for UNIQUE Constraint

Unique Identifiers: Ensure columns like email, phone number, or social security number are unique.
Prevent Duplicate Entries: Avoid duplicate rows for combinations of data (e.g., product and category).
Database Normalization: Enforce data integrity and normalization rules.

Common Errors and Solutions

Inserting Duplicate Values
- Error: Violation of UNIQUE KEY constraint. Cannot insert duplicate key.
- Solution: Check for existing records before inserting new data.
Example:
```
INSERT INTO users (username, email)
VALUES ('johndoe', 'john@example.com')
ON CONFLICT DO NOTHING;
```
Handling NULL Values
- A UNIQUE column allows only one NULL value. If more than one NULL value is attempted, it results in a constraint violation.

Performance Considerations

Indexing: The UNIQUE constraint automatically creates an index on the constrained column(s), improving query performance but slightly increasing storage requirements.
Column Selection: Apply UNIQUE constraints only to columns where uniqueness is essential, as maintaining the index incurs overhead during insert or update operations.

Conclusion

The SQL UNIQUE constraint is a powerful tool for maintaining data integrity by ensuring that column values remain distinct. Whether used on a single column or multiple columns, it helps enforce rules and avoid data duplication. Understanding when and how to use the UNIQUE constraint can greatly enhance your database design.

Souy Soeng

SQL UNIQUE Constraint