How to Create Check Constraint on Multiple Columns in SQL Server - SQL Server / TSQL Tutorial Part 84

Scenario:

You are working as SQL Server developer, you need to create dbo.Customer table that should have First Name, Age and Age Type columns. You can add CustomerId as Identity. You need to create Check Constraint on Age and Age Type Columns with below logic

<65 agetype="Adult" and="" p="">
If true then let the record insert or update otherwise fail due to Check Constraint.

<65 agetype="Adult" and="" p=""> Solution:

<65 agetype="Adult" and="" p=""> Below script can be used to add Check Constraint on multiple columns according to our requirement.

CREATE TABLE dbo.Customer (
    CustomerId INT identity(1, 1)
    ,NAME VARCHAR(100)
    ,Age INT
    ,AgeType VARCHAR(15)
    ,CONSTRAINT dbo_Customer_AgeAndAgeType CHECK (
        (
            Age <= 17
            AND AgeType = 'Child'
            )
        OR (
            Age > 17
            AND Age < 65
            AND AgeType = 'Adult'
            )
        OR (
            Age >= 65
            AND AgeType = 'Senior'
            )
        )
    )

Let's insert few records and try to see if Check Constraint is working as expected.

--Correct values accrording to Constraint
insert into dbo.Customer(Name,Age,AgeType)
Values('Najaf',13,'Child')
go

--Wrong values according to Check Constraint
insert into dbo.Customer(Name,Age,AgeType)
Values('Leena',14,'Adult')
go
--Correct values accroding to Constraint
insert into dbo.Customer(Name,Age,AgeType)
Values('Raza',30,'Adult')
go
--Wrong values according to Check Constraint
insert into dbo.Customer(Name,Age,AgeType)
Values('Aamir',30,'Senior')
go
--Wrong values according to Check Constraint
insert into dbo.Customer(Name,Age,AgeType)
Values('John',65,'Adult')
go
--Correct values accroding to Constraint
insert into dbo.Customer(Name,Age,AgeType)
Values('Kris',65,'Senior')
go

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 25
The INSERT statement conflicted with the CHECK constraint "dbo_Customer_AgeAndAgeType". The conflict occurred in database "YourDatabaseName", table "dbo.Customer".
The statement has been terminated.

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 33
The INSERT statement conflicted with the CHECK constraint "dbo_Customer_AgeAndAgeType". The conflict occurred in database "YourDatabaseName", table "dbo.Customer".
The statement has been terminated.
Msg 547, Level 16, State 0, Line 37
The INSERT statement conflicted with the CHECK constraint "dbo_Customer_AgeAndAgeType". The conflict occurred in database "YourDatabaseName", table "dbo.Customer".
The statement has been terminated.

(1 row(s) affected)

Let's check the data in table by using select query. As can be seen below the only records are inserted which passed the Check Constraint. Rest of the records could not be inserted.

How to create Check Constraint on Multiple Columns in SQL Server Table

 Let's try to update the records and see if Check Constraint is working as expected.

update dbo.Customer
set Age=30
where Customerid=1

It failed with below error as we can not have Age 30 for AgeType='Child' according to our Check Constraint logic.

Msg 547, Level 16, State 0, Line 18
The UPDATE statement conflicted with the CHECK constraint "dbo_Customer_AgeAndAgeType". The conflict occurred in database "YourDatabaseName", table "dbo.Customer".
The statement has been terminated.

Video Demo : How to create Check Constraint on Multiple Columns in SQL Server