Unique constraints in SQL Server

This tutorial explains how to create, add and delete unique constraints in SQL Server.

What is the only constraint in SQL Server?

The only constraint is a field or combination of data fields, uniquely identifying a record. Some fields may contain NULL values, as long as the combination of values ​​is unique.

Difference between unique constraint and primary key

Primary Key Primary Key Unique Unique Constraint constraint No field in the primary key is allowed to contain NULL values. Some fields of unique constraints may contain NULL values, provided that the combination of values ​​is unique.

Create a unique constraint with the CREATE TABLE command

 CREATE TABL E ten_bang 
 ( 
  cot1 kieudulieu [ NULL | NOT NULL ]; 
  cot2 kieudulieu [ NULL | NOT NULL ]; 
  … 
  CONSTRAINT ten_rangbuoc UNIQUE (cot1_rb, cot2_rb, … cot_n_rb) 
 ); 

ten_bang

The name of the table you want to create

cot1, cot2

The column you want to create in the table

ten_rangbuoc

Name of unique binding

cot1_rb, cot2_rb, . cot_n_rb

The columns make up the unique constraint.

Data types in SQL Server

For example

 CRE ATE TABLE nhanvien 
 ( id_nhanvien INT PRIMARY KEY, 
  so_nhanvien INT NOT NULL, 
  ho VARCHAR(50) NOT NULL, 
  ten VARCHAR(50), 
  luong MONEY, 
  CONSTRAINT nhanvien_duynhat UNIQUE (so_nhanvien) 
 ); 

In this example, we have created a unique constraint named nhanvien_duynhat on the table created by the CREATE TABLE command, including a single field so_nhanvien.

Can create unique constraints with more than 1 field as in the example below.

 CREATE TABL E nhanvien 
 ( id_nhanvien INT PRIMARY KEY, 
  so_nhanvien INT NOT NULL, 
  ho VARCHAR(50) NOT NULL, 
  ten VARCHAR(50), 
  luong MONEY, 
  CONSTRAINT nhanvien_duynhat UNIQUE (ho, ten) 
 ); 

Create a unique constraint with the ALTER TABLE command

 ALTER T ABLE ten_bang 
 ADD CON STRAINT ten_rangbuoc UNIQUE (cot1, cot2, . cot_n); 

ten_bang

The name of the table you want to edit. This is the table you want to add unique constraints.

ten_rangbuoc

The name of the unique constraint that you want to create.

cot1, cot2 . cot_n

The columns make up the unique constraint.

For example

 ALTER TABL E nhanvien 
 ADD CONSTR AINT nhanvien_duynhat UNIQUE (so_nhanvien); 

The above example uses the ALTER TABLE command to create a unique constraint on an existing table, called nhanvien, called so_nhanvien field. To create constraints with more than 1 field, see the example below.

 ALTER TABLE nhanvi en 
 ADD CONSTRAINT ten _nhanvien_duynhat UNIQUE (cough, ten); 

Delete unique binding

Syntax

 ALTER TABLE ten_ bang 
 DROP CONSTRAINT ten_rangbuoc; 

ten_bang

The name of the table you want to edit. This is the table you want to delete the unique constraint.

ten_rangbuoc

The name of the unique constraint that you want to delete.

For example

 ALTER TABL E nhanvien 
 DROP CONST RAINT nhanvien_duynhat; 

The example above removes the unique constraint named nhanvien_duynhat on the table.

Previous post: Activate foreign key in SQL Server

Lesson: Check constraints in SQL Server