SQL UNIQUE Constraint

Introduction

 
In this chapter, we will how to use a SQL Unique Constraint statement with various options.  
 

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 
  1. CREATE TABLE EmployeeName  (        
  2.  EmpID int NOT NULL UNIQUE,        
  3.   EmpName varchar(255) NOT NULL,         
  4. );     
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
  1. CREATE TABLE Employee  (      
  2.  EmpID int NOT NULL UNIQUE,      
  3.   EmpName varchar(255) NOT NULL,      
  4.   EmpFirstName varchar(255),      
  5.   EmpLastname varchar(255),      
  6.   EmpAge int      
  7. );     
The above query created a table with the name "Employee" and the first column name EmpId is Not NULL and UNIQUE, other column names EmpName, EmpFirstName, EmpLastname, EmpAge
 
Using Unique constraint on the alter table
 
Syntax 
  1. ALTER TABLE Employee       
  2. 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 
  1. ALTER TABLE Employee       
  2. 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  
  1. ALTER TABLE Employee       
  2. 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:
  1. In Object Explorer, right-click the table to which you want to add a unique constraint, and click Design.
  2. On the Table Designer menu, click Indexes/Keys.
  3. In the Indexes/Keys dialog box, click Add.
  4. In the grid under General, click Type and choose Unique Key from the drop-down list box to the right of the property.
  5. On the File menu, click Save table name. 

Using unique constraint in SQL

 
To create a unique constraint,
  1. In Object Explorer, connect to an instance of Database Engine.
  2. On the Standard bar, click New Query.
  3. 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 
  1. USE sample ;        
  2. GO        
  3. CREATE TABLE SampleDetails        
  4.  (        
  5.    TransactionID int NOT NULL,         
  6.    CONSTRAINT AK_TransactionID UNIQUE(TransactionID)         
  7. );         
  8. 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

  1. In Object Explorer, connect to an instance of Database Engine.
  2. On the Standard bar, click New Query.
  3. 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 
  1. USE sample          
  2. GO        
  3. ALTER TABLE Person.Password         
  4. ADD CONSTRAINT AK_Password UNIQUE (PasswordHash, PasswordSalt);         
  5. GO        
The above query appends the table Person. Password in the sample database, with the AK_Password CONSTRAINT which makes PasswordHash, and PasswordSalt unique.
 

To create a unique constraint in a new table

  1. In Object Explorer, connect to an instance of Database Engine.
  2. On the Standard bar, click New Query.
  3. 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  
  1. USE sample;        
  2. GO        
  3. CREATE TABLE Production.TransactionHistoryArchive2        
  4. (        
  5.    TransactionID int NOT NULL,        
  6.    CONSTRAINT AK_TransactionID UNIQUE(TransactionID)        
  7. );        
  8. 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 the next chapter, we will learn how to use a SQL Count statement with various options.
Author
Naresh Beniwal
Editor 7.1k 1.5m
Next » SQL COUNT Statement

©2024 C# Corner.