SQL UNIQUE Constraint

SQL Unique Statement

The Unique constraint statement ensures that all values in a column are different. Both the Unique and Primary Key constraints provide a guarantee for uniqueness for a column or set of columns. A Primary key constraint automatically has a unique constraint in SQL. However, you can have many unique constraints per table, but only one primary key constraint per table.

We can create a unique constraint in SQL Server 2019 (15.x) by using SQL server management studio or SQL to ensure no duplicate values are entered in specific columns that do not participate in a primary key. Creating a unique constraint automatically creates a corresponding unique index in SQL.

SQL Server Unique constraints allow you to ensure that the data stored in a column, or a group of columns, is unique among the rows in a table.

Syntax

CREATE TABLE EmployeeName (
EmpID int NOT NULL UNIQUE,
EmpName varchar(255) NOT NULL,
);

The above query creates a table with the name "EmployeeName" and column name EmpID which is both Not Null and Unique(i.e we cannot have empty or duplicate data) and EmpName

Using Unique constraint on create table statement

Syntax

CREATE TABLE Employee (
EmpID int NOT NULL UNIQUE,
EmpName varchar(255) NOT NULL,
EmpFirstName varchar(255),
EmpLastname varchar(255),
EmpAge int
);

The above query created a table with the name "Employee" and the first column name EmpId is Not NULL and UNIQUE, other column name EmpName,EmpFirstName,EmpLastname, EmpAge

Using Unique constraint on alter table

Syntax

ALTER TABLE Employee
ADD UNIQUE (EmpID);

The above query with add a column EmpID and make it UNIQUE.

"Unique" is used to signify a Unique constraint, and also to define a unique name a Unique constraint,on multiple columns.

Syntax

ALTER TABLE Employee
ADD CONSTRAINT UC_Employee UNIQUE (EmpID,EmpLastName);

The above query will add EmpID and EmpLastName columns into the UC_Employee table, with the Unique Constraint

Using Drop a Unique constraint statement

Use the following example to drop a Unique constraint:

Syntax

ALTER TABLE Employee
DROP CONSTRAINT UC_Employee;

The above query will remove the "Unique" Constrain from the Employee table.

Using SQL Server Management Studio in Unique Constraint

To create a unique constraint statement:

In Object Explorer, right-click the table to which you want to add a unique constraint, and click Design.
On the Table Designer menu, click Indexes/Keys.
In the Indexes/Keys dialog box, click Add.
In the grid under General, click Type and choose Unique Key from the drop-down list box to the right of the property.
On the File menu, click Save table name.

Using unique constraint in SQL

To create a unique constraint,

In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute. This example creates the table SampleDetails and creates a unique constraint on the column TransactionID.

Syntax

USE sample ;
GO
CREATE TABLE SampleDetails
(
TransactionID int NOT NULL,
CONSTRAINT AK_TransactionID UNIQUE(TransactionID)
);
GO

The above query will create a table SampleDetails in the sample database, with TransactionID and the AK_TransactionID constraint which makes TransactionID unique

To create a unique constraint on an existing table

In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute. The example creates a unique constraint on the columns PasswordHash and PasswordSalt in the table Person.Password.

Syntax

USE sample
GO
ALTER TABLE Person.Password
ADD CONSTRAINT AK_Password UNIQUE (PasswordHash, PasswordSalt);
GO

The above query appends the table Person.Password in the sample database , with the AK_Password CONSTRAINT which makes PasswordHash amd PasswordSalt unique.

To create a unique constraint in a new table

In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute. The example creates a table and defines a unique constraint on the column TransactionID.

Syntax

USE sample;
GO
CREATE TABLE Production.TransactionHistoryArchive2
(
TransactionID int NOT NULL,
CONSTRAINT AK_TransactionID UNIQUE(TransactionID)
);
GO

The above query uses the sample database and creates a table with the name "Production.TransactionHistoryArchive2" and column name TransactionID and CONSTRAINT AK_TransactionID UNIQUE column name is TransactionID.

Summary

In this article, you learned how to use a SQL Unique Constraint statement with various options.