SQL UNIQUE Constraint

-- create a table with unique constraint on college_code column
CREATE TABLE Colleges (
  college_code VARCHAR(20) UNIQUE,
  college_name VARCHAR(50)
);

SQL UNIQUE Constraint Syntax

The syntax of the SQL UNIQUE constraint is:

CREATE TABLE table_name (
  column_name data_type UNIQUE
);

Here,

• table_name is the name of the table to be created
• column_name is the name of the column where the constraint is to be implemented
• data_type is the data type of the column such as INT, VARCHAR, etc.

Create UNIQUE Constraint

We can implement the UNIQUE constraint at the time of table creation. For example,

-- create a table with unique constraint
CREATE TABLE Colleges (
  college_id INT NOT NULL UNIQUE,
  college_code VARCHAR(20) UNIQUE,
  college_name VARCHAR(50)
);

-- insert values to Colleges table
INSERT INTO Colleges(college_id, college_code, college_name)
VALUES (1, "ARD12", "Star Public School"), (2, "ARD13", "Galaxy School");

Here, both college_id and college_code have the UNIQUE constraint.

The INSERT INTO command runs successfully as we have inserted unique values to college_id and college_code.

UNIQUE Constraint With Alter Table

We can also add the UNIQUE constraint to an existing column using the ALTER TABLE command. For example,

For a Single Column

-- add unique constraint to an existing column
ALTER TABLE Colleges
ADD UNIQUE (college_id);

Here, the SQL command adds the UNIQUE constraint to the colleges_id column in the existing Colleges table.

For Multiple Columns

-- add unique constraint to multiple columns 
ALTER TABLE Colleges
ADD UNIQUE Unique_College (college_id, college_code);

Here, the SQL command adds the UNIQUE constraint to college_id and college_code columns in the existing Colleges table.

Also, Unique_College is a name given to the UNIQUE constraint defined for college_id and college_code columns.

Note: Our online SQL editor doesn't support this action as it is based on SQLite.

Error When Inserting Duplicate Values

We will get an error if we try to insert duplicate values in a column with the UNIQUE constraint.

-- create a table named colleges
CREATE TABLE Colleges (
  college_id INT NOT NULL UNIQUE,
  college_code VARCHAR(20) UNIQUE,
  college_name VARCHAR(50)
);

-- insert values to Colleges table
-- college_code has duplicate values
INSERT INTO Colleges(college_id, college_code, college_name)
VALUES (1, "ARD12", "Star Public School"), (2, "ARD12", "Galaxy School");

Here, we are trying to insert ARD12 into the college_code column in two different rows. Hence, the INSERT INTO command results in an error.

CREATE UNIQUE INDEX for Unique Values

If we want to create indexes for unique values in a column, we use the CREATE UNIQUE INDEX constraint. For example,

-- create unique index
CREATE UNIQUE INDEX college_index
ON Colleges(college_code);

Here, the SQL command creates a unique index named college_index on the Colleges table using the college_code column.

Note: Creating an index does not alter the original data in the table.

Recommended Readings

• SQL CREATE INDEX
• SQL Constraints
• SQL NOT NULL Constraint
• SQL PRIMARY KEY