SQL Unique Key

A Unique Key in SQL is a constraint that ensures all values in a column or a set of columns are distinct from each other. Unlike the primary key, a unique key allows for NULL values but ensures that no two non-null values are the same.

Unique keys are used to maintain the integrity of data by preventing duplicate values in a column (or set of columns). They can be applied to one or more columns, and each unique key in a table must have a unique value for its columns across all rows.

Tables Used

Example with Unique Key

In this example, we will create a Customers table where the Email column has a unique constraint. This ensures that no two customers can have the same email address, preventing duplicate entries for the same email.

    -- Create the Customers table with a UNIQUE constraint on the Email column
    CREATE TABLE Customers (
        CustomerID INT PRIMARY KEY,
        CustomerName VARCHAR(100),
        Email VARCHAR(100) UNIQUE
    );
    
    -- Insert data into the Customers table
    INSERT INTO Customers (CustomerID, CustomerName, Email) VALUES (101, 'Alice', 'alice@example.com');
    INSERT INTO Customers (CustomerID, CustomerName, Email) VALUES (102, 'Bob', 'bob@example.com');
    INSERT INTO Customers (CustomerID, CustomerName, Email) VALUES (103, 'Charlie', 'charlie@example.com');
    
    -- View the Customers table
    SELECT * FROM Customers;
            

Output Table

Explanation

- Unique Key: The Unique Key constraint ensures that the values in the Email column are unique across all rows. In this case, no two customers can share the same email address.
- Customers Table: The Customers table has a unique constraint applied to the Email column. This prevents any duplication of email addresses in the database.
- Data Insertion: We insert several rows into the Customers table. The unique key ensures that each email address is distinct.
- Output: The resulting Customers table shows the unique combination of CustomerID and Email in each row, with no duplicate email addresses.