UNIQUE Constraint

SQL UNIQUE Constraint

In SQL, a UNIQUE constraint is used to ensure that all values in a column (or a combination of columns) are unique across all rows in a table. This means that no two rows can have the same value(s) in the specified column(s).

Here's how you define a UNIQUE constraint:

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

Or you can add a UNIQUE constraint to an existing table:

A few important points about UNIQUE constraints:

1. Uniqueness across rows: The UNIQUE constraint ensures that all values in the specified column(s) are unique across all rows in the table.

2. Nullable columns: In most SQL databases, if a column with a UNIQUE constraint allows NULL values, the UNIQUE constraint will allow multiple NULL values in that column. However, it will enforce uniqueness for non-NULL values.

3. Composite unique constraints: You can apply a UNIQUE constraint across multiple columns, creating a composite unique constraint. This means the combination of values across those columns must be unique.

4. Automatic index creation: In many database systems, a UNIQUE constraint will automatically create an index on the column(s) involved. This helps enforce the uniqueness efficiently and can speed up queries that involve those columns.

Here's an example of creating a table with a UNIQUE constraint:

            CREATE TABLE employees (
                employee_id INT UNIQUE,
                first_name VARCHAR(50),
                last_name VARCHAR(50),
                email VARCHAR(100) UNIQUE
            );
        

In this example, employee_id and email columns are marked as UNIQUE, ensuring that no two employees can have the same employee_id or email address.