In SQL, the UNIQUE
constraint in a column means the column must have unique values.
-- create a table with unique constraint on college_code column
CREATE TABLE Colleges (
college_code VARCHAR(20) UNIQUE,
college_name VARCHAR(50)
);
Here, the values of the college_code column must be unique.
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 createdcolumn_name
is the name of the column where the constraint is to be implementeddata_type
is the data type of the column such as INT
, VARCHAR
, etc.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
.
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.
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.
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.