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.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.