Unique Constraint in SQL

Unique Constraint in SQL

The Unique Constraint is an essential tool in relational databases for ensuring that the data stored in a column (or a combination of columns) is unique across all rows in a table. This constraint helps maintain data integrity by preventing duplicate values where uniqueness is required.

---

1. What is a Unique Constraint?

• Definition: A unique constraint is a rule that ensures that the values in a column (or a set of columns) are distinct from one another. In other words, no two rows in the table can have the same value(s) in the column(s) that have been defined with a unique constraint.

• Purpose: The primary purpose of the unique constraint is to prevent duplicate entries in a table. This is especially useful for columns that should store unique data, such as email addresses, usernames, or product codes.

Key Characteristics:

1. Uniqueness: The unique constraint guarantees that all values in the specified column(s) are unique.

2. Multiple Unique Constraints: A table can have multiple unique constraints, ensuring the uniqueness of different columns or combinations of columns.

3. Allows NULL Values: Unlike primary keys, unique constraints allow NULL values. However, depending on the database system, multiple NULL values may be considered unique.

---

2. Defining a Unique Constraint

You can define a unique constraint when creating a table or by modifying an existing table. The unique constraint can be applied to a single column or a combination of columns (composite unique constraint).

a. Unique Constraint on a Single Column

When defining a unique constraint on a single column, you can use the UNIQUE keyword directly after the column definition.

Example:

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

In this example:

• The email column is defined with a unique constraint, ensuring that no two users can have the same email address.

b. Composite Unique Constraint

Sometimes, you may want to ensure that a combination of values in multiple columns is unique. This is known as a composite unique constraint.

Example:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    order_date DATE,
    UNIQUE (product_id, order_date)
);

In this example:

• The combination of product_id and order_date must be unique. This ensures that no two orders for the same product are recorded on the same date.

---

3. Enforcing Unique Constraints

Once a unique constraint is defined, the database automatically enforces it whenever data is inserted or updated. If you try to insert a duplicate value in a column (or set of columns) with a unique constraint, the database will throw an error.

a. Valid Insertion:

INSERT INTO users (user_id, email, username, password)
VALUES (1, '[e-mail]', 'amitsharma', 'password123');

• This insertion is valid because there is no other record with the email [e-mail].

b. Invalid Insertion (Unique Constraint Violation):

INSERT INTO users (user_id, email, username, password)
VALUES (2, '[e-mail]', 'amitkumar', 'password456');

• This insertion will fail because the email [e-mail] already exists in the table. The unique constraint on the email column prevents this duplicate entry.

c. Handling NULL Values:

In most SQL databases, a unique constraint allows NULL values. However, the handling of multiple NULL values can vary by database system:

• MySQL: Allows multiple NULL values in a column with a unique constraint.

• PostgreSQL: Allows multiple NULL values in a column with a unique constraint.

• SQL Server: Treats NULL as a unique value, allowing only one NULL.

Example:

INSERT INTO users (user_id, email, username, password)
VALUES (3, NULL, 'rajkumar', 'password789');

• This insertion is valid, and NULL can be entered into the email column because the unique constraint allows it.

---

4. Adding a Unique Constraint to an Existing Table

If you need to add a unique constraint to an existing table, you can use the ALTER TABLE command.

a. Adding a Unique Constraint:

ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);

• This command adds a unique constraint to the email column in the users table.

b. Dropping a Unique Constraint:

If you need to remove a unique constraint from a table, you can use the ALTER TABLE command with DROP CONSTRAINT.

ALTER TABLE users
DROP CONSTRAINT unique_email;

• This command removes the unique constraint from the email column.

---

5. Benefits of Using Unique Constraints

• Data Integrity: Ensures that critical fields, such as email addresses or product codes, remain unique across the table, preventing duplicates.

• Flexibility: Allows you to enforce uniqueness on both individual columns and combinations of columns, providing flexibility in your database design.

• Enhanced Searching: Unique constraints can improve query performance when searching for specific values, as the database knows the value must be unique.

---

Conclusion

The unique constraint is a powerful feature in SQL that helps maintain data integrity by ensuring that specific columns (or combinations of columns) contain unique values. Whether you’re preventing duplicate email addresses, ensuring unique product codes, or enforcing more complex business rules, understanding and using unique constraints is essential for designing robust databases.

This tutorial is brought to you by codes with pankaj.