SQL UNIQUE Constraint

 The UNIQUE constraint in SQL is used to ensure that all values in a column (or a combination of columns) are unique across rows. It prevents duplicate values from being inserted into a column. The UNIQUE constraint can be applied to a single column or multiple columns (composite unique constraint).

Syntax:

CREATE TABLE table_name (
    column1 datatype UNIQUE,
    column2 datatype,
    ...
);

Example:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    name VARCHAR(100)
);

In this example, the email column must contain unique values. If an attempt is made to insert a duplicate email, it will result in an error.

Composite UNIQUE Constraint:

A unique constraint can also be applied to a combination of columns. This ensures that the combination of values in these columns is unique across all rows in the table.

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

Here, the combination of order_id and product_id must be unique, meaning that no two rows can have the same pair of values for these columns.

Notes:

• The UNIQUE constraint does not allow NULL values, but a column with a UNIQUE constraint can still allow multiple NULL values (depending on the database system).
• The UNIQUE constraint is similar to the PRIMARY KEY constraint, but a table can have multiple UNIQUE constraints, while it can only have one PRIMARY KEY.