Tutorial Gateway

  • C Language
  • Java
  • R
  • SQL
  • MySQL
  • Python
  • BI Tools
    • Informatica
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • QlikView
  • Js

SQL Check Constraint

by suresh

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 T-SQL Query, and SQL Server Management Studio with examples.

For this SQL Check Constraint 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.

SQL Check Constraint 1

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

SQL CODE

USE [SQL Tutorial]
GO

UPDATE [dbo].[CustomerRecords]
SET [Age] = -150
WHERE [CustKey] = 4
GO

OUTPUT

SQL Check Constraint 2

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.

Create SQL Check Constraint using SSMS

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

SQL Check Constraint 3

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

SQL Check Constraint 4

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.

SQL Check Constraint 5

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

SQL Check Constraint 6

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

SQL Check Constraint 7

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.

SQL Check Constraint 8

Let me save the table design

SQL Check Constraint 9

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)

SQL Check Constraint 10

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.

SQL Check Constraint 11

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

SQL Check Constraint 12

Insert Values into SQL Server Check Constraint Column

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

SQL CODE

-- Sql Server Check Constraint example
USE [SQL Tutorial]
GO
INSERT INTO [dbo].[CustomerRecords]
          ([CustKey], [FirstName], [LastName], [Age], [EmailAddress], [Yearly Income], [Profession])
   VALUES (5, 'SQL', 'Tutorials',  175, '[email protected]', 92500, 'Author')

OUTPUT

SQL Check Constraint 13

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]
GO
INSERT INTO [dbo].[CustomerRecords]
          ([CustKey], [FirstName], [LastName], [Age], [EmailAddress], [Yearly Income], [Profession])
   VALUES (5, 'SQL', 'Tutorials',  -17, '[email protected]', 92500, 'Author')

OUTPUT

SQL Check Constraint 14

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

USE [SQL Tutorial]
GO
INSERT INTO [dbo].[CustomerRecords]
          ([CustKey], [FirstName], [LastName], [Age], [EmailAddress], [Yearly Income], [Profession])
   VALUES (5, 'SQL', 'Tutorials',  17, '[email protected]', 92500, 'Author')

OUTPUT

SQL Check Constraint 15

Let us see the total records in our table.

USE [SQL Tutorial]
GO
SELECT [CustKey]
      ,[FirstName]
      ,[LastName]
      ,[Age]
      ,[EmailAddress]
      ,[Yearly Income]
      ,[Profession]
  FROM [CustomerRecords]

OUTPUT

SQL Check Constraint 16

SQL Check Constraint on Existing table using SQL Query

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

SQL Check Constraint 19

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 in SQL.

-- Creating SQL Server CHECK Constraint
USE [SQL Tutorial]
GO
 
ALTER TABLE[CustomerRecords]
ADD CONSTRAINT CK_CustomerRecords_Age CHECK([Age] > 0 AND [Age] < 120)
 
GO

OUTPUT

SQL Check Constraint 20

Let me insert negative value

SQL CODE

USE [SQL Tutorial]
GO
INSERT INTO [dbo].[CustomerRecords]
	  ([CustKey], [FirstName], [LastName], [Age], [EmailAddress], [Yearly Income], [Profession])
     VALUES (6, 'SQL', 'Server',  -25, '[email protected]', 92500, 'Author')

OUTPUT

SQL Check Constraint 21

Insert NULLS in SQL Check Constraint

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

SQL CODE

USE [SQL Tutorial]
GO
INSERT INTO [dbo].[CustomerRecords]
	  ([CustKey], [FirstName], [LastName], [Age], [EmailAddress], [Yearly Income], [Profession])
     VALUES (6, 'SQL', 'Server',  NULL, '[email protected]', 2500, 'SQL Author')

OUTPUT

SQL Check Constraint 22

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.

SQL Check Constraint 23

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]
GO
 
ALTER TABLE[CustomerRecords]
ADD CONSTRAINT CK_CustomerRecords_AgeAndIncome CHECK([Age] >= 18 AND [YearlyIncome] <= 100000)
 
GO

Create SQL Check Constraint on table creation

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]
GO

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)
)
GO

OUTPUT

SQL Check Constraint 26

Delete SQL Check Constraint using Query

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

-- Delete SQL Server CHECK Constraint
USE [SQL Tutorial]
GO
 
ALTER TABLE [dbo].[CustomerRecords]   
DROP CONSTRAINT CK_CustomerRecords_Age;  
GO

OUTPUT

SQL Check Constraint 24

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

-- Deleting SQL Server CHECK Constraint
USE [SQL Tutorial]
GO
-- To find the CHECK Constraint Name
SELECT name, create_date   
FROM sys.check_constraints
WHERE OBJECT_NAME(parent_object_id) = N'CustomerRecords';  
GO

OUTPUT

SQL Check Constraint 25

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.

SQL Check Constraint 17

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 in Sql Server.

SQL Check Constraint 18

Thank You for Visiting Our Blog

Placed Under: SQL

Stay in Touch!

Sign Up to receive Email updates on Latest Tutorials

  • C Programs
  • Java Programs
  • SQL FAQ’s
  • Python Programs
  • SSIS
  • Tableau
  • JavaScript

Copyright © 2019 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy