How to Make a Column Unique in SQL?

Problem:

You would like to make a column unique in a given table in a database.

Example:

We would like to make the column name unique in the table product. The query below presents one way to do it.

Solution 1: Creating new table with a UNIQUE constraint

CREATE TABLE product (
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(100) UNIQUE, 
  producer VARCHAR(100), 
  category VARCHAR(100)
);

Discussion:

In this example a given column (the column name) was made unique by adding the clause UNIQUE at the end of the column definition (name VARCHAR(100) UNIQUE). This new table (in our example: product) will contain the column (in our example: name) that stores unique values in rows.

Solution 2: Adding a UNIQUE constraint to an existing table

ALTER TABLE product 
ADD CONSTRAINT UQ_product_name UNIQUE(name);

Discussion:

In this example the table product already exists. We want to modify this table and add a UNIQUE constraint to column name. This is possible by using the ALTER TABLE clause. First we write ALTER TABLE, then we list the name of the table (in our example: product), and next we add the clause ADD CONSTRAINT with the name of the UNIQUE constraint (in our example: UQ_product_name). This is followed by the UNIQUE keyword with column/columns (in our example it is column: name) in parentheses.

You can also make a multiple-column UNIQUE constraint. Here’s how:

Solution 3: Adding a multi-column UNIQUE constraint to an existing table

ALTER TABLE product 
ADD CONSTRAINT UQ_product_name_producer UNIQUE(name, producer);

Discussion:

In this example the table product is changed by using the ALTER TABLEclause and the clause ADD CONSTRAINT with the name of the UNIQUE constraint (in our example: UQ_product_name_producer) followed by the UNIQUE keyword with the list of columns (in our example there are two columns: name and producer) in parentheses.