Articles Cookbook
Back to list
Standard SQL

How to make a column unique in SQL?

Database:

Operators:

UNIQUE, ADD CONSTRAINT, ALTER TABLE

Problem:

You would like to make a given 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

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 definition column (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:

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:

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 TABLE clause 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.

Recommended courses:

Recommended articles:

See also:

go to top