SQL Check Constraint

SQL Check Constraint will limit the data that is inserted into the SQL table columns. For example, if we want to restrict the number of digits in a phones number, or limit the customer age between 18 to 60 then we can assign Check Constraint to that column. In this article we will show you, how to assign Check Constraint in SQL server using SQL Query, and SQL Server Management Studio with examples.

For this demonstration we are going to use the Customer Records table inside the SQL Tutorial database. Below screenshot will show you the data inside the table.

Let me update the Age for Custkey = 4. Please refer UPDATE Statement to understand the below statement.

SQL CODE

USE [SQL Tutorial]

UPDATE [dbo].[CustomerRecords]

SET [Age] = -150

WHERE [CustKey] = 4

OUTPUT

Although the age of any person is between 0 and 100 in most cases, or 0 to 150 in rare cases. Our Age column is accepting negative values which is not correct in real-time. In order to deal with this situation we have to use the SQL Server Check Constraint.

Creating SQL Check Constraint using SSMS

Before we start working with the Check constraint let me show you the final data inside our table:

In this example we will show you, How to create a Check Constraint in SQL server using the SQL server Management Studio. To do so, Please go to the Object Explorer.

Within the object explorer, Expand the Database folder in which our table is located. Please select the table on which you want to create a Check Constraint (here it is CustomerRecords). Next, go to the Constraints folder, and Right click on it will open the context menu. Please select New Constraint… option as shown below

Once you select New Constraint… option, SQL management studio will open the corresponding table in design mode, and it will display a Check Constraint pop up window.

In order to enable Check constraint, we have to provide a Boolean expression inside the Expression property. So, let me click on the … button beside the Expression.

As you can see we are writing a simple expression Age > 0 AND Age < 120. It means, age should be between 0 and 120

Next, we renamed the Check constraint name as CK_CustomerRecords_Age. Please change the Name as per your requirements, and provide a valid description. Apart from these three, there is one more important property:

Check Existing Data On Creation or Re-Enabling: This property has Boolean Yes, or No options. If you set it as Yes then check constraint will test the expression against the existing records otherwise, it will check for new records only. Let me keep it as Default Yes.

Let me save the table design

As you can see from the below screenshot, it is throwing an error. Because, Customer Records table had one value that is not satisfying the expression (-150 is not between 0 and 120)

Let me change the Check Existing Data On Creation or Re-Enabling property to No. It means, it will not care about existing records, Check constraint will only test the expression for new records. OR you can Delete, or Update the record containing the -150 value.

Now you can see our newly created Check constraint. For this, Go to the object explorer -> Database – > Table Name -> Expand Constraints Folder

Insert Values into SQL Server Check Constraint Column

Let me insert value into the table using the INSERT Statement.

SQL CODE

USE [SQL Tutorial]

INSERT INTO [dbo].[CustomerRecords]

([CustKey], [FirstName], [LastName], [Age], [EmailAddress], [Yearly Income], [Profession])

VALUES (5, 'SQL', 'Tutorials', 175, '[email protected]', 92500, 'Author')

OUTPUT

As you can see from the above code snippet, it is returning an Error. This is because, we are inserting Age value as 175, and 175 is not between 0 and 120.

Let us try with negative value. As you can se, it is also throwing the same error.

SQL CODE

USE [SQL Tutorial]

INSERT INTO [dbo].[CustomerRecords]

([CustKey], [FirstName], [LastName], [Age], [EmailAddress], [Yearly Income], [Profession])

VALUES (5, 'SQL', 'Tutorials', -17, '[email protected]', 92500, 'Author')

OUTPUT

This time we will try with correct value, and you can see our insertion is successful.

SQL CODE

USE [SQL Tutorial]

INSERT INTO [dbo].[CustomerRecords]

([CustKey], [FirstName], [LastName], [Age], [EmailAddress], [Yearly Income], [Profession])

VALUES (5, 'SQL', 'Tutorials', 17, '[email protected]', 92500, 'Author')

OUTPUT

Let us see the total records in our table.

SQL CODE

USE [SQL Tutorial]

SELECT [CustKey]

,[FirstName]

,[LastName]

,[Age]

,[EmailAddress]

,[Yearly Income]

,[Profession]

FROM [CustomerRecords]

OUTPUT

SQL Check Constraint on Existing table using SQL Query

Before we start working with the Check constraint let me show you the final data inside our table:

In this example we will show you, How to add SQL Server check constraint on existing table. For this, we are going to use the above specified table.

Now let me add the check constraint. As you can see from the below screenshot we are using the Alter Table Statement to alter the table content, and then used the ADD CONSTRAINT statement to add the check constraint.

SQL CODE

-- Creating SQL Server CHECK Constraint

USE [SQL Tutorial]

ALTER TABLE[CustomerRecords]

ADD CONSTRAINT CK_CustomerRecords_Age CHECK([Age] > 0 AND [Age] < 120)

OUTPUT

Let me insert negative value

SQL CODE

USE [SQL Tutorial]

INSERT INTO [dbo].[CustomerRecords]

([CustKey], [FirstName], [LastName], [Age], [EmailAddress], [Yearly Income], [Profession])

VALUES (6, 'SQL', 'Server', -25, '[email protected]m', 92500, 'Author')

OUTPUT

Insert NULLS in SQL Check Constraint

In this example we will show you, what will happen when we insert the NULL values into the Check constraint column. To demonstrate the same, Let me insert NULL value into the Age Address Column.

SQL CODE

USE [SQL Tutorial]

INSERT INTO [dbo].[CustomerRecords]

([CustKey], [FirstName], [LastName], [Age], [EmailAddress], [Yearly Income], [Profession])

VALUES (6, 'SQL', 'Server', NULL, '[email protected]', 2500, 'SQL Author')

OUTPUT

From the below screenshot you can see, it has inserted the NULL value into the check constraint. Because, expression result for NULL value returns undefined (Neither TRUE, Nor FALSE) so, Check constraint allows the record.

Create SQL Check Constraint on Multiple Columns

In this example we will show you, How to create a SQL check constraint on multiple columns. Below code will restrict the user not is enter Age less than 18, and Yearly income greater than 100000

SQL CODE

-- Creating SQL Server CHECK Constraint

USE [SQL Tutorial]

ALTER TABLE[CustomerRecords]

ADD CONSTRAINT CK_CustomerRecords_AgeAndIncome CHECK([Age] >= 18 AND [YearlyIncome] <= 100000)

Create SQL Check Constraint using SQL Query

In this example we will show you, How to create a check constraint in SQL server at the time of table creation. I Suggest you to refer Create Table article to understand the Create Table statement.

SQL CODE

USE [SQL Tutorial]

CREATE TABLE [dbo].[CustomerRecords2](

[CustKey] [int] NOT NULL PRIMARY KEY,

[FirstName] [varchar](50) NOT NULL,

[LastName] [varchar](50) NULL,

[Age] [int] NULL,

[EmailAddress] [nvarchar](50) NULL,

[Yearly Income] [money] NULL,

[Profession] [nvarchar](100) NULL,

CONSTRAINT CK_CustomerRecords_AgeColumn CHECK([Age] > 0 AND [Age] < 120)

)

OUTPUT

Delete SQL Check Constraint using SQL Query

If you know the Check constraint name that you want to delete, use the DROP Constraint statement along with the ALTER TABLE Statement

SQL CODE

-- Deleting SQL Server CHECK Constraint

USE [SQL Tutorial]

ALTER TABLE [dbo].[CustomerRecords]

DROP CONSTRAINT CK_CustomerRecords_Age;

OUTPUT

If you don’t know the check constraint name then use the following SELECT Statement to get the Constraint name.

SQL CODE

-- Deleting SQL Server CHECK Constraint

USE [SQL Tutorial]

-- To find the CHECK Constraint Name

SELECT name, create_date

FROM sys.check_constraints

WHERE OBJECT_NAME(parent_object_id) = N'CustomerRecords';

OUTPUT

Once you got the Check constraint name, you can use the ALTER TABLE Statement to Drop the Check constraint.

Delete SQL Check Constraint using SSMS

Within the object explorer, Expand the Database folder in which the table had. Next, expand the CustomerRecords table to find the constraints folder. Right click on the Constraints folder, and select the Delete option as we shown below.

Once you select the Delete option, a new window called Delete Object will be opened as we shown below. Click OK to delete the check constraint.

Thank You for Visiting Our Blog

Creating SQL Check Constraint using SSMS

Insert Values into SQL Server Check Constraint Column

SQL Check Constraint on Existing table using SQL Query

Insert NULLS in SQL Check Constraint

Create SQL Check Constraint on Multiple Columns

Create SQL Check Constraint using SQL Query

Delete SQL Check Constraint using SQL Query

Delete SQL Check Constraint using SSMS

Leave a Reply Cancel reply