SQL UNIQUE Constraint Tutorial

Example Table

We will create a table named employees to demonstrate the SQL UNIQUE constraint:

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

Creating the Table

To create the table, use the following SQL command:

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

Result:

Command	CREATE TABLE employees (employee_id INT PRIMARY KEY, email VARCHAR(100) UNIQUE, first_name VARCHAR(50), last_name VARCHAR(50));
Result	A table named employees is created with email column set to UNIQUE.

Inserting Data

To insert data into the table, use the following SQL command:

    INSERT INTO employees (employee_id, email, first_name, last_name) 
    VALUES (1, 'john.doe@example.com', 'John', 'Doe');
    

Result:

Command	INSERT INTO employees (employee_id, email, first_name, last_name) VALUES (1, 'john.doe@example.com', 'John', 'Doe');
Result	Data is inserted into the employees table.

Attempting to Insert Duplicate Unique Value

If you try to insert a duplicate value into the UNIQUE column, you will get an error:

    INSERT INTO employees (employee_id, email, first_name, last_name) 
    VALUES (2, 'john.doe@example.com', 'Jane', 'Smith');
    

Result:

Command	INSERT INTO employees (employee_id, email, first_name, last_name) VALUES (2, 'john.doe@example.com', 'Jane', 'Smith');
Result	Error: Duplicate entry 'john.doe@example.com' for key 'email'.

Important Considerations

When using the UNIQUE constraint, consider the following:

• UNIQUE constraints ensure that all values in a column are different.
• A table can have multiple UNIQUE constraints, but each must be applied to different columns.
• UNIQUE constraints help maintain data integrity by preventing duplicate values in specified columns.