UNIQUE Key in SQL: Constraint & Syntax Explained with FAQs
Unique Key is a database constraint that ensures all the values in a column or a combination of columns are distinct. It prevents two records from having the same values in a unique Key column or set.
Both Unique Key and Primary Key ensure uniqueness in a table. The main differences are that a Unique Key allows NULL values but requires each non-null value to be unique and multiple unique keys can exist in a table while a Primary Key can not allow NULL value and only one primary key is allowed per table.
Single Column Unique Key
A single-column unique key ensures that values in one column are unique. This is typically used to prevent duplicate entries in a column where data redundancy is not acceptable.
Syntax :-
CREATE TABLE TableName( column1 datatype UNIQUE KEY, column2 datatype, ..... );
CREATE TABLE TableName ( column1 datatype, column2 datatype, ... UNIQUE KEY (column1) );
CREATE TABLE TableName( column1 datatype UNIQUE KEY, column2 datatype UNIQUE KEY, column3 datatype, ..... );
CREATE TABLE TableName ( column1 datatype, column2 datatype, column3 datatype, ... UNIQUE KEY (column1), UNIQUE KEY (column2) );
Example
Using above SQL query,Let's create an Employee table with column EmployeeId,Name, Mobile, JobTitle, Department and salary. To ensure no two employees can have the same mobile number, we are creating a Unique Key constraint on column Mobile.
CREATE TABLE Employees (
EmployeeId INT PRIMARY KEY ,
Name VARCHAR(50) NOT NULL,
Mobile VARCHAR(20) UNIQUE KEY,
JobTitle VARCHAR(100) NOT NULL,
Department VARCHAR(100) NOT NULL,
Salary DECIMAL(10, 2)
);Verification
Since we have created Employees table with Unique key constraints on Mobile column so we can not enter duplicate Mobile in the table. So if we attempt to insert a duplicate Mobile number into the table, the database will throw an error.
INSERT INTO Employees VALUES (1,"Ram", "9876543212","SDE","Tech",4.5), (2,"Rajesh", "9876543212","OM","OPR",7.5);
Error Code: 1062. Duplicate entry '9876543212' for key 'Employee.Mobile' 0.016 sec
Composite Unique Key
A composite unique key (or multi-column unique key) is created by combining multiple columns. it ensures uniqueness across the combined column data. This type is typically used in scenarios where the combination of two or more columns must be unique.
Syntax :-
CREATE TABLE TableName ( column1 datatype, column2 datatype, column3 datatype, ... UNIQUE KEY (column1, column3) );
Example
Using above SQL query, Let's create an Employees table with column EmployeeId, Name, Mobile, JobTitle, Department and Salary. To ensure no two employees can have the same combination of Name and Mobile, we are creating a Composite Unique Key constraint on column Name and Mobile.
CREATE TABLE Employees (
EmployeeId INT PRIMARY KEY,
Name VARCHAR(50),
Mobile VARCHAR(20),
JobTitle VARCHAR(100) ,
Department VARCHAR(100),
Salary DECIMAL(10, 2),
UNIQUE KEY (Name, Mobile)
);Verification
Since we have created Employees table with Composite Unique key constraints on Name and Mobile columns. So if we are attempt to insert unique Name and Mobile number separately we can do this.
Inserting duplicate entry of Mobile column
INSERT INTO EmployeeV2 VALUES (1,"jack", "9876543212","SDE","Tech",4.5), (2,"Head", "9876543212","OM","OPR",7.5);
There will be no error in above query execution both records will be inserted in table sucessfully
Let's take other case, inserting duplicate name in Name column
INSERT INTO Employees VALUES (1,"Ram", "9876543211","SDE","Tech",4.5), (2,"Ram", "9876543213","OM","OPR",7.5);
The above query also executed successfully without any problem.
Going forward, if we attempt to insert a duplicate combination of Name and Mobile, the database will throw an error.
INSERT INTO Employees VALUES (1,"Ram", "9876543211","SDE","Tech",4.5), (2,"Ram", "9876543211","OM","OPR",7.5);
Error Code: 1062. Duplicate entry 'Ram-9876543211' for key 'Employees.Name' 0.032 sec
Unique Key on an Existing Column
If you want to create Unique Key on an existing table/column. You can do this by following below syntax.
ALTER TABLE TableName ADD CONSTRAINT UNIQUE_KEY_NAME UNIQUE (column(s));
If you attempt to add a unique key constraint to a column that already contains duplicate values, the database will throw an error.
For example, consider a case where you created Employees table without any unique key constraints and inserted the following records.
INSERT INTO Employees VALUES (1,"Ram", "9876543211","SDE","Tech",4.5), (2,"Ram", "9876543211","OM","OPR",7.5);
Now, if you try to add a unique key constraint on the Name column, the database will throw an error because the column contains duplicate value for "Ram"
ALTER TABLE Employees ADD CONSTRAINT UNIQUE_KEY_NAME UNIQUE (Name);
Error Code: 1062. Duplicate entry 'Ram' for key 'Employees.UNIQUE_KEY_NAME' 0.047 sec
Dropping an SQL Unique Key
If you want to drop Unique Key from table. You can do this by following below syntax.
ALTER TABLE TableName DROP CONSTRAINT UNIQUE_KEY_NAME;
ALTER TABLE TableName DROP INDEX ColumnName;
Handling NULL Values in Unique Key Constraints
Handling NULL values in Unique Key constraint can vary across different SQL databases, Different SQL databases have different rules regarding how many NULL values can exist in a column with a unique constraint.
- SQL Server :- Allows only one NULL value for columns with a unique constraint.
- MySQL :- Allows multiple NULL values.
- PostgreSQL :- Allows multiple NULL values in columns with unique constraints
Key Points of Unique Key
- Unique Key constraint is crucial for maintaining data integrity by ensuring the uniqueness of data.
- A table can have more than one unique key.
- It can also be used as a foreign key in another table.
- Avoid using the Unique Key on columns which values may change over time.
- When we create a unique key on a column or set of columns, the database automatically creates a unique index to ensure the uniqueness of the values.
- A unique index is similar to a regular index but with the additional rule that no two rows can have the same value in the indexed column(s).
Frequently Asked Questions (FAQs)
1. What is a UNIQUE key in SQL?
A UNIQUE key in SQL is a constraint that ensures all values stored in a column or a combination of columns, are distinct. It prevents duplicate entries while still allowing NULL values depending on the database. UNIQUE keys are commonly used to enforce business rules such as preventing duplicate email IDs, phone numbers or usernames in a table.
2. How is a UNIQUE key different from a PRIMARY key?
Both UNIQUE and PRIMARY keys enforce uniqueness, but they serve different purposes. A PRIMARY key does not allow NULL values and a table can have only one primary key. A UNIQUE key can allow NULL values and multiple unique keys can exist in a single table. PRIMARY keys are used for row identity, while UNIQUE keys enforce additional data rules.
3. Can a table have multiple UNIQUE keys?
Yes, a table can have multiple UNIQUE key constraints. This is useful when more than one column must remain unique independently. For example, a Users table may require both Email and Mobile columns to be unique. Each UNIQUE key is enforced separately and helps maintain strong data integrity.
4. Can a UNIQUE key contain NULL values?
Yes, a UNIQUE key can contain NULL values, but behavior differs by database. MySQL and PostgreSQL allow multiple NULL values, while SQL Server allows only one NULL. Since NULL represents an unknown value, most databases do not treat multiple NULLs as duplicates.
5. What is a composite UNIQUE key?
A composite UNIQUE key is a constraint created using two or more columns together. It ensures that the combination of values is unique, not the individual columns themselves. This is useful when enforcing uniqueness across related fields.
6. Does creating a UNIQUE key automatically create an index?
Yes. When you create a UNIQUE key, the database automatically creates a unique index on the column or columns. This index enforces uniqueness and improves query performance for searches, joins and lookups.
7. Can a UNIQUE key be used as a foreign key?
Yes, a UNIQUE key can be referenced by a foreign key in another table. While foreign keys usually reference PRIMARY keys, SQL also allows referencing UNIQUE keys when the column is guaranteed to be unique.
8. Can I add a UNIQUE key to an existing table?
Yes, you can add a UNIQUE key to an existing table using ALTER TABLE. However, the column or columns must already contain unique values. If duplicates exist, the database will reject the operation.
9. What happens if I insert duplicate data into a UNIQUE key column?
If you attempt to insert or update a duplicate value in a UNIQUE key column, the database will throw an error and reject the operation. This protects data integrity by preventing duplicates.
10. Can a UNIQUE key be dropped later?
Yes, a UNIQUE key can be dropped using ALTER TABLE. In MySQL this is often done with DROP INDEX, while other databases use DROP CONSTRAINT. Dropping a UNIQUE key should be done carefully because it removes protection against duplicate data.
11. Should UNIQUE keys be used on frequently changing columns?
It is generally not recommended to use UNIQUE keys on columns that change frequently. Updates require additional checks and index maintenance, which can impact performance. UNIQUE keys are best for stable identifiers.
12. Can UNIQUE keys improve query performance?
Yes, UNIQUE keys improve query performance by creating unique indexes. These indexes speed up lookups and joins. However, they may slightly slow down INSERT and UPDATE operations due to uniqueness checks.
13. Are UNIQUE keys mandatory in every table?
No, UNIQUE keys are not mandatory for every table, but they are highly recommended wherever duplicate data is not acceptable. They help ensure data correctness and consistency.
14. Can a UNIQUE key be created on text or VARCHAR columns?
Yes, UNIQUE keys can be created on VARCHAR or text-based columns if the database supports indexing on them. However, very long text columns can affect performance, so shorter normalized values are preferred.
15. Are UNIQUE keys commonly asked in SQL interviews?
Yes, UNIQUE keys are a common interview topic. Interviewers often ask about differences between UNIQUE and PRIMARY keys, NULL handling, composite unique keys and real-world use cases.