In SQL, the
UNIQUE constraint in a column means that the column must have unique values. For example,
CREATE TABLE Colleges ( college_id INT NOT NULL UNIQUE, college_code VARCHAR(20) UNIQUE, college_name VARCHAR(50) );
Here, the values of the college_code column must be unique. Similarly, the values of college_id must be unique as well as it cannot store
UNIQUE Vs DISTINCT
UNIQUE constraint is used to make column's value unique. However, to select unique rows from the table, we have to use SQL SELECT DISTINCT. For example,
SELECT DISTINCT country FROM Customers;
Here, the SQL command selects unique countries from the Customers table.
Count UNIQUE Rows
SELECT COUNT(DISTINCT country) FROM Customers;
Here, the SQL command returns the count of unique countries.
UNIQUE Constraint With Alter Table
We can also add the
UNIQUE constraint to an existing column using the ALTER TABLE command. For example,
For single column
ALTER TABLE Colleges ADD UNIQUE (college_id);
For multiple column
ALTER TABLE Colleges ADD UNIQUE UniqueCollege (college_id, college_code);
Here, the SQL command adds the
UNIQUE constraint to the specified column(s) in an existing table.
Error When Inserting Duplicate Values
If we try to insert duplicate values in a column with the
UNIQUE constraint, we will get an error.
CREATE TABLE Colleges ( college_id INT NOT NULL UNIQUE, college_code VARCHAR(20) UNIQUE, college_name VARCHAR(50) ); -- Inserting values to the Colleges table 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 in 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: Although the index is created for only unique values, the original data in the table remains unaltered.