How to Create Check Constraint on Single Column in SQL Server - SQL Server / TSQL Tutorial Part 83

Scenario : 

You are working as SQL Sever developer, You are preparing scripts to create Tables in database. As part of these scripts you need to create Check Constraints but you want to follow the company's naming standards for Check Constraints. How would you add Check Constraint Name in your scripts.

Solution:

SQL Server automatically give name to Check Constraint if we don't provide. Let's execute the below script and see what name SQL Server assign to Check Constraint when we don't provide the name. In below example we are creating Check Constraint on FName and making sure it only accepts alphabets.

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


We can use system views to gather information related to Check Constraints.
--How to get Check Constraints in SQL Server
SELECT
    * 
    FROM INFORMATION_SCHEMA.Check_Constraints


How to create Check Constraint in SQL Server with default Name


Let's say that the name SQL Server provided to Check Constraint is not according to our company standards. Our standards say that the Check Constraint should follow below pattern
Start with Chk_SchemaName_TableName_ColumnName_CheckConstraintDescription.  To add a Check Constraint  , your syntax will be
Constraint Constraint_Name Check LogicForCheckConstraint.

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


Run the select query on system view to get Check Constraint information. I suggest to create objects with some naming convention or standards instead of letting the sql server decide name for your objects.
How to create Check Constraint by providing name according to your company standards in SQL Server


Video Demo : How to Create Check Constraint by using Naming Convention in SQL Server

No comments:

Post a Comment