Introduction
SQL Restraints In SQL, unique constraints are used to determine whether the result of the sub-query contains duplicate tuples. It gives back a Boolean value that indicates whether duplicate tuples are present or not. If there are duplicate tuples in the subquery, the unique constraint returns false; otherwise, it returns true.
Crucial Points
On an empty subquery, evaluates to true.
Returns true only if the subquery's output contains unique tuples (two tuples are unique if the values of any of their attributes differ).
Gives a true result if there are two duplicate entries in the subquery and at least one of the attributes is NULL.
Syntax
CREATE TABLE table_name
(
column1 data_type UNIQUE
)
Example
CREATE TABLE Employee
(
Id INT NOT NULL,
[Name] VARCHAR(50) NOT NULL,
[Code] INT NOT NULL,
EmailAddress VARCHAR(100) UNIQUE
)
UNIQUE Constraint with Alter Table
We can also add the UNIQUE constraint to an existing column using the ALTER TABLE SQL command.
For a Single Column
Syntax
ALTER TABLE table_name
ADD UNIQUE (column1);
Example
ALTER TABLE Employee
ADD UNIQUE (EmailAddress);
For Multiple Columns
Syntax
ALTER TABLE table_name
ADD UNIQUE (column1, column2);
Example
ALTER TABLE Employee
ADD UNIQUE ([Code], EmailAddress);
Error when inserting the duplicate values
If we attempt to add duplicate entries to a column that has the UNIQUE constraint, we will receive an error.
INSERT INTO Employee
VALUES (1, 'Jaimin Shethiya', 00001, '[email protected]')
, (2, 'Jaimin Shethiya', 00001, '[email protected]')
![Insert]()
Create a unique index for unique values
The generate UNIQUE INDEX requirement is used to generate indexes for unique values in a column.
Syntax
CREATE UNIQUE INDEX index_name
ON table_name(column1);
Example
CREATE UNIQUE INDEX code_index
ON Employee(Code);
DROP Unique Constraint
Let's say we have to remove that column from the table specifically.
Syntax
ALTER TABLE table_name
DROP INDEX index_name;
Example
ALTER TABLE Employee
DROP INDEX code_index;
FAQs
Q1. What does SQL mean by a unique constraint?
The uniqueness of each value in a column is guaranteed by a UNIQUE constraint. Uniqueness guarantees for a column or group of columns are provided by UNIQUE and PRIMARY KEY constraints. Unique constraints are inherently present in PRIMARY KEY constraints.
We learned the new technique and evolved together.
Happy coding!