SQL UNIQUE

What is UNIQUE?

The UNIQUE constraint in SQL is used to ensure that all values in a column are different from each other. It prevents duplicate values in the specified column(s) of a table.

Syntax of UNIQUE

The basic syntax for adding a UNIQUE constraint is:

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

Example of Using UNIQUE

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

CREATE TABLE Users (
  UserID INT PRIMARY KEY,
  Username VARCHAR(50) UNIQUE,
  Email VARCHAR(100) UNIQUE
);

This ensures that both the Username and Email fields cannot have duplicate values across the table.

Adding UNIQUE to Existing Tables

If you need to add a UNIQUE constraint to an existing column, you can use the following command:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name);

For example:

ALTER TABLE Users
ADD CONSTRAINT unique_email UNIQUE (Email);

Important Considerations

When using the UNIQUE constraint, keep in mind:

• A table can have multiple UNIQUE constraints, but only one primary key.
• NULL values are allowed in unique columns, as long as the other values are unique (except in some SQL implementations).
• Be cautious when inserting data to avoid violations of the UNIQUE constraint, which will result in errors.