SQL Tutorial

✨ SQL UNIQUE Constraint

The UNIQUE constraint in SQL ensures that all values in a column are distinct, meaning no two rows can have the same value in a column with a UNIQUE constraint. It helps maintain data integrity by preventing duplicate records in a table.

1️⃣ Adding UNIQUE Constraint to a Column

You can add the UNIQUE constraint to a column when creating a table or modifying an existing one. Here’s how you can do it during table creation:

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

In this example, the email column cannot have duplicate values. Every user must have a unique email address.

2️⃣ Inserting Data with UNIQUE Constraint

When you insert data into a column with a UNIQUE constraint, SQL will enforce that no duplicate values are allowed. Here's what happens when you try to insert duplicate data:

INSERT INTO users (user_id, username, email)
VALUES (1, 'john_doe', 'john@example.com');

INSERT INTO users (user_id, username, email)
VALUES (2, 'jane_doe', 'john@example.com');

The second insert will throw an error because the email value "john@example.com" already exists in the table, violating the UNIQUE constraint.

3️⃣ ALTER Table to Add UNIQUE Constraint

If a column already exists and you want to add the UNIQUE constraint, you can modify the table using the ALTER TABLE statement. Here's an example:

ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);

This will apply the UNIQUE constraint to the email column if it wasn't added during table creation.

4️⃣ UNIQUE Constraint with Multiple Columns

You can also apply the UNIQUE constraint to a combination of columns. This ensures that the combination of values in those columns is unique across all rows.

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

This will prevent duplicate combinations of first_name and last_name in the employees table.

🔧 Key Considerations

• NULL Values: The UNIQUE constraint allows multiple NULL values because NULL is treated as a unique value.
• Performance: While UNIQUE constraints ensure data integrity, they may impact performance on large tables due to indexing.
• Combining Constraints: You can combine the UNIQUE constraint with other constraints like NOT NULL and PRIMARY KEY for more precise control over data.