What is Check Constraint in SQL Server - SQL Server / TSQL Tutorial Part 82

What is Check Constraint : 

Check Constraints limit the values that are accepted by one or more columns.


Let's understand Check Constraint with real time scenario. 

You are working as SQL Server developer, You need to create dbo.Customer table which should have columns such as FName, LName and Address. As FName is going be be VARCHAR so the users can insert string values that can contain alphabets, numeric and other characters. You want to write a Check Constraint so FName only accepts alphabets.

Solution:

Let's create the table with Check Constraint by using below script

--Create Table with 
use YourDatabaseName
go
Create table dbo.Customer
(
FName VARCHAR(100) Not Null,
LName VARCHAR(100),
StreetAddress VARCHAR(255),
Check (FName not like '%[^a-z]%')
)


Let's insert some records in table and see if our Check Constraint is working as expected. It should let only insert those records in which FName only contains alphabets.

--insert some sample records
insert into dbo.Customer
(FName,LName,StreetAddress)
Values ('Aamir','Shahzad','xyz address')
go
insert into dbo.Customer
(FName,LName,StreetAddress)
Values ('Aamir4','Shahzad','xyz address')
go
insert into dbo.Customer
(FName,LName,StreetAddress)
Values ('abc3c','Shahzad','xyz address')
go
insert into dbo.Customer
(FName,LName,StreetAddress)
Values ('-tName','Shahzad','xyz address')

When I executed above insert scripts, below are the messages I received from SQL Server.

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 18
The INSERT statement conflicted with the CHECK constraint "CK__Customer__FName__5FB337D6". The conflict occurred in database "YourDatabaseName", table "dbo.Customer", column 'FName'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 22
The INSERT statement conflicted with the CHECK constraint "CK__Customer__FName__5FB337D6". The conflict occurred in database "YourDatabaseName", table "dbo.Customer", column 'FName'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 26
The INSERT statement conflicted with the CHECK constraint "CK__Customer__FName__5FB337D6". The conflict occurred in database "YourDatabaseName", table "dbo.Customer", column 'FName'.
The statement has been terminated.

It only inserted very first record that contains all alphabets for FName, rest of the records are rejected by our Check Constraint as they contain other characters than alphabets.

Let's check the data in table to make sure only single record is inserted.
What is Check Constraint in SQL Server and how to Create Check Constraint in SQL Server

Video Demo: What is Check Constraint and How to create Check Constraint in SQL Server

1 comment:

  1. hello sir this a great videos for learning, m looking forward your videos on store producer ( advance and complex ) as well writing complex query

    ReplyDelete