How to create and delete MSSQL unique constraints

Setting a unique constraint on a column ensures data integrity for the database by preventing duplicate values from entering certain columns.

Unique constraint features

• Value uniqueness: Unique constraints do not allow duplicate values, so you can ensure that you have unique values for specific columns.
• Allow NULL: Unique constraints do not allow redundancy, but allow NULL values, but only one NULL value per column.
• FORIGN KEY REFERENCE: The unique constraint has the advantage of being referred to as FORIGN KEY.
• Index creation: Adding a unique constraint at the time of index creation can automatically check the uniqueness of the value.

Create a unique constraint

1. How to create a unique automatically in a column while creating a table.

Syntax and Source code.

-- Syntax
CREATE TABLE [Table Name] (
  [column1] [dataType] UNIQUE,
  [column2] [dataType],
  [column3] [dataType] 
);
-- Source code. 
USE sampleDB;
DROP TABLE IF EXISTS UserInfoForUnique;
CREATE TABLE UserInfoForUnique (
  ID int UNIQUE,
  LastName varchar(255),
  FirstName varchar(255)    
);
-- Check Error.
insert into UserInfoForUnique(ID,LastName,FirstName)
values(202401,'john','hub');

It's a practice code.

2. Generating UNIQUE constraints on two or more columns.

Syntax and Source code.

-- Syntax
CREATE TABLE [Table Name] (
  [column1] [dataType],
  [column2] [dataType],
  [column3] [dataType],
  CONSTRAINT [Constraint name] UNIQUE([column1,column2]) 
);
-- Source code.   
USE sampleDB;
DROP TABLE IF EXISTS UserInfoForUnique;
CREATE TABLE UserInfoForUnique (
  ID int, 
  LastName varchar(255),
  FirstName varchar(255),
  CONSTRAINT Uk_UserInfoForUnique UNIQUE(ID,FirstName)  
);

3.How to create a UNIQUE constraint on an existing column.

Syntax and Source code.

-- Syntax
ALTER TABLE [Table Name] ADD CONSTRAINT [Constraint name] UNIQUE ([column1,column2]);
-- Source code.
USE sampleDB;
DROP TABLE IF EXISTS UserInfoForUnique;
CREATE TABLE UserInfoForUnique (
  ID int, 
  LastName varchar(255),
  FirstName varchar(255) 
); 
ALTER TABLE UserInfoForUnique  ADD CONSTRAINT Uk_UserInfoForUnique UNIQUE (ID,FirstName);

4.How to set up UNIQUE when creating a new column. Syntax and Source code.

-- Syntax.
ALTER TABLE [Table Name] ADD [column1] [dataType] CONSTRAINT [UNIQUE Name] UNIQUE (column1,column2]);
--Source code. 
USE sampleDB;
DROP TABLE IF EXISTS UserInfoForUnique;
CREATE TABLE UserInfoForUnique (
  ID int, 
  LastName varchar(255),
  FirstName varchar(255) 
);

ALTER TABLE UserInfoForUnique ADD addId int  CONSTRAINT Uk_UserInfoForUnique UNIQUE (ID,addId);

How to search for created unique constraints

Syntax and Source code.

-- Syntax
EXEC SP_HELP[Table Name];
-- Source code
EXEC SP_HELP UserInfoForUnique;
-- Syntax code
select CONSTRAINT_CATALOG
      ,CONSTRAINT_SCHEMA
      ,CONSTRAINT_NAME
      ,TABLE_CATALOG
      ,TABLE_SCHEMA
      ,TABLE_NAME
      ,COLUMN_NAME
      ,ORDINAL_POSITION
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where TABLE_NAME  = 'Table Name';

-- Source code 
USE sampleDB;
DROP TABLE IF EXISTS UserInfoForUnique;
CREATE TABLE UserInfoForUnique (
      ID int unique, 
      LastName varchar(255),
      FirstName varchar(255) 
);

select CONSTRAINT_CATALOG
      ,CONSTRAINT_SCHEMA
      ,CONSTRAINT_NAME
      ,TABLE_CATALOG
      ,TABLE_SCHEMA
      ,TABLE_NAME
      ,COLUMN_NAME
      ,ORDINAL_POSITION
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where TABLE_NAME  = 'UserInfoForUnique' ; 

It's a practice code.

Drop created unique constraints

Syntax and Source code.

-- drop Syntax.
ALTER TABLE [Table Name] DROP CONSTRAINT [UNIQUE Name]
 
-- Source code.
USE sampleDB;
DROP TABLE IF EXISTS UserInfoForUnique;
CREATE TABLE UserInfoForUnique (
  ID int, 
  LastName varchar(255),
  FirstName varchar(255),
  CONSTRAINT Uk_UserInfoForUniqueDrop UNIQUE(ID,FirstName)  
);
ALTER TABLE UserInfoForUnique  DROP CONSTRAINT Uk_UserInfoForUniqueDrop;