MS SQL Server: Check Constraints in SQL Server

What is the check constraint in SQL Server, what is it used for and what is it used for? This guide will give you the answer.

What is Check Constraint check constraint in SQL Server?

The check constraint in SQL Server (Transact-SQL) allows defining conditions for each row in the table.

Note

• Test constraints cannot be defined in SQL View.
• The check constraint in the table must refer to the column in that table, unable to reference the column in another table.
• Check constraints cannot use Subquery subqueries.
• Test constraints can be defined by the CREATE TABLE or ALTER TABLE command.

Create check constraints with the CREATE TABLE command

Syntax

 CREATE TABLE ten _bang 
 ( 
  cot1 kieudulieu [ NULL | NOT NULL ], 
  cot2 kieudulieu [ NULL | NOT NULL ], 
  … 
 
  CONSTRAINT ten_rangbuoc 
  CHECK [ NOT FOR REPLICATION ] (dieu_kien ten_cot) 
 ); 

Data types in SQL Server

ten_bang

The name of the table that wants to create check constraints.

ten_rangbuoc

The name you want to set for check binding.

ten_cot

The table column that the check constraint applies.

condition

Conditions must meet.

As an example,

 CREA TE TABLE nhanvien 
 (id_nhanvien INT NOT NULL, 
  ho VARCHAR(50) NOT NULL, 
  ten VARCHAR(50), 
  luong MONEY, 
  CONSTRAINT id_nhanvien_kiemtra 
  CHECK (id_nhanvien BETWEEN 1 AND 10000) 
 ); 

In this example, the CREATE TABLE statement creates a check constraint named id_nhanvien_kiemtra in the table. This constraint will ensure that the id_nhanvien information field contains a value between 1 and 10000.

This is another example.

 CRE ATE TABLE nhanvien 
 (id_nhanvien INT NOT NULL, 
  ho VARCHAR(50) NOT NULL, 
  ten VARCHAR(50), 
  luong MONEY, 
  CONSTRAINT luong_kiemtra 
  CHECK (luong > 0) 
 ); 

This example creates a constraint that checks the checksum in the table, ensuring that the salary will be greater than zero.

Create check constraints with the ALTER TABLE command

Syntax

 ALTER TABLE ten_bang 
 ADD CONSTAINT ten_rangbuoc 
  CHECK (d ieu_kien ten_cot); 

ten_bang

The name of the table wants to add check constraints.

ten_rangbuoc

Name set for check binding.

ten_cot

Columns in the table that check constraints apply.

condition

Conditions that check constraints must meet.

As an example,

This is an example of using the ALTER TABLE command to create check constraints in SQL Server.

 ALTER TABLE  nhanvien 
 ADD CONSTRAINT ho_kiemtra 
  CHECK (ho IN ('S mith', 'Anderson', 'Jonas')); 

The check constraint ho_kiemtra is created on the existing table, ensuring that the employee's surname will contain only values for Smith, Anderson or Jonas.

Delete check constraints

Syntax

 ALTE R TABLE ten_bang 
 DROP CONSTRAINT ten_rangbuoc 

ten_bang

The name of the table to delete the check constraint.

ten_rangbuoc

Check binding name wants to delete.

As an example,

 ALTE R TABLE nhanvien 
 DROP CONSTRAINT ho_kiemtra; 

This command will delete the constraint ho_kiemtra on the table.

Enable check binding

Syntax

 ALTER TA BLE ten_bang 
 WITH CHEC K CHECK CONSTRAINT ten_rangbuoc; 

ten_bang

The name of the table to re-enable the check constraint.

ten_rangbuoc

The name of the check constraint needs to be activated.

As an example,

 ALTER TA BLE nhanvien 
 WITH CHEC K CHECK CONSTRAINT luong_kiemtra; 

This example re-activates the check constraint on the table in the table.

Disable check binding

Syntax

 ALTER TA BLE ten_bang 
 NOCHECK CONSTRAINT ten_rangbuoc; 

ten_bang

The name of the table to disable check constraints.