SQL Query to Add Unique key Constraints Using ALTER Command

Here we will see how to add unique key constraint to a column(s) of a MS SQL Server's database with the help of a SQL query using ALTER clause.

For the demonstration purpose, we will be creating a demo table in a database called "geeks".

Creating the Database :

Use the below SQL statement to create a database called geeks:

CREATE DATABASE geeks;

Using the Database :

Use the below SQL statement to switch the database context to geeks:

USE geeks;

Table Definition :

We have the following demo table in our geeks database.

CREATE TABLE demo(
  ID INT IDENTITY(1,1) PRIMARY KEY, --IDENTITY(1,1) is same as AUTO_INCREMENT in MySQL.
                                      --Starts from 1 and increases by 1 with each inserted row.
  NAME VARCHAR(30) NOT NULL,
  PHONE VARCHAR(10) NOT NULL
);

You can use the below statement to query the description of the created table:

EXEC SP_COLUMNS demo;

Adding data to the table :

Use the below statement to add data to the demo table:

INSERT INTO demo --no need to mention columns explicitly as we are inserting into all columns and ID gets 
                 --automatically incremented.
VALUES
('Yogesh Vaishnav', '000000001'),
('Ajit Yadav', '000000002'),
('Ashish Yadav', '000000003'),
('Vishal Vishwakarma', '000000004'),
('Suhana Shaikh', '000000005');

To verify the contents of the table use the below statement :

SELECT * FROM demo;

Now let's add an unique key constraint to column phone as phone no. should be unique.

NOTE: There can be multiple unique key columns but only one primary key column in a database table.

Syntax for adding the unique key constraint to single as well as multiple columns is given below:

Syntax :

--Adding unique key constraint to a column.

ALTER TABLE <table_name>

ADD UNIQUE (<column_name>);

--Adding unique key constraint to multiple columns

ALTER TABLE <table_name>

ADD  CONSTRAINT <identifier_name> UNIQUE (<column_name1>, <column_name2>,...);

Example :

ALTER TABLE demo
ADD UNIQUE (PHONE);

--Let's insert a row into the table.

INSERT INTO demo
VALUES ('GeeksforGeeks','000000001'); --error

As string '000000001' already exist in the phone column which has an unique key constraint now, thus when executing the above query results into an error.

Thus we can say that the unique key constraint has been successfully applied.