SQL CHECK Constraint

SQL CHECK Constraint


The SQL CHECK constraint is used to limit the value range that can be placed in a table field or column.

The SQL CHECK constraint allows you to enables a condition on each new row entered in a table. If the condition evaluates to false, the row isn't entered into the table.



SQL check constraint used for alter table check constraint, sql constraint check, disable or drop, add or remove check constraint.

SQL CHECK Constraint on CREATE TABLE

The following SQL statement creates a SQL CHECK constraint on the "BookPrice" field when the "Books" table is created. The SQL CHECK constraint specifies that the field "BookPrice" must only include integers greater than 100.

For MySql


CREATE TABLE Books
(
BookID INT,
BookName VARCHAR(255),
AuthorName VARCHAR(255),
BookPrice DECIMAL (8, 2) NOT NULL,
BookDomain VARCHAR(255),
CHECK (BookPrice > 100)
);

For Microsoft SQL Server / Oracle / Microsoft Access


CREATE TABLE Books
(
BookID INT,
BookName VARCHAR(255),
AuthorName VARCHAR(255),
BookPrice DECIMAL (8, 2) NOT NULL CHECK (BookPrice > 100),
BookDomain VARCHAR(255)
);

In the above example, the following SQL statement creates a new table called Books and adds five fields.

Here, BookPrice field has a SQL CHECK constraint to validate newly entered records.

To insert records on "Books" table, use the following SQL statements:


INSERT INTO Books (BookID, BookName, AuthorName, BookPrice, BookDomain)
VALUES (111, 'Sql Complete Programming','Suresh Babu', 160, 'Programming');
INSERT INTO Books (BookID, BookName, AuthorName, BookPrice, BookDomain)
VALUES (222, 'Sql Database Hacking', 'Vinoth Kumar', 75, 'Hacking');
INSERT INTO Books (BookID, BookName, AuthorName, BookPrice, BookDomain)
VALUES (333, 'Pl Sql Quick Reference','Siva Kumar', 220, 'Programming');
INSERT INTO Books (BookID, BookName, AuthorName, BookPrice, BookDomain)
VALUES (444, 'Sql Database Analysis','Suresh Babu', 180, 'Database');

The SQL statement above would insert three(but there are four records tried to insert) new records into the "Books" table.

The second record will not inserted into table. Bcoz the record doesn't match the criteria of the BookPrice column. The value of BookPrice column must have greater than "100".

After executing above query, the "Books" table look like this:

BookId BookName AuthorName BookPrice BookDomain
111 Sql Complete Programming Suresh Babu 160 Programming
333 Pl Sql Quick Reference Siva Kumar 220 Programming
444 Sql Database Analysis Suresh Babu 180 Database

To allow naming of a SQL CHECK constraint, and for defining a SQL CHECK constraint on multiple fields, use the following SQL syntax:

For Microsoft SQL Server / Oracle / Microsoft Access / MySql


CREATE TABLE Books
(
BookID INT,
BookName VARCHAR(255),
AuthorName VARCHAR(255),
BookPrice DECIMAL (8, 2) NOT NULL,
BookDomain VARCHAR(255),
CONSTRAINT chk_Price CHECK (BookPrice > 100 AND BookDomain = 'Database')
);

SQL CHECK Constraint on ALTER TABLE

To create a SQL CHECK constraint on the "BookPrice" field when the table is already created, use the following SQL statement:

For MySql / Microsoft SQL Server / Oracle / Microsoft Access


ALTER TABLE Books
ADD CHECK (BookPrice > 100);

To create a SQL CHECK constraint on multiple fileds when the table is already created, use the following SQL statement:

For MySql / Microsoft SQL Server / Oracle / Microsoft Access


ALTER TABLE Books
ADD CONSTRAINT chk_Price CHECK (BookPrice > 100 AND BookDomain = 'Database');

To DROP a SQL CHECK Constraint

To drop or remove a SQL CHECK constraint, use the following SQL statement:

For MySQL


ALTER TABLE Books
DROP CHECK chk_Price;

For Microsoft SQL SERVER / Microsoft ACCESS / Orcale


ALTER TABLE Books
DROP CONSTRAINT chk_Price;


Sql check keyword using with sql server check constraint examples, check constraint expression, verify gender, check age, date greater than, list of values, multiple column validation.

SQL Check Constraint Case

Example 1: Sql Check constraint with case statement:

Suppose that the bank has a policy of setting the credit limit for residents of Louisiana at less than $150,000. We'll accomplish this by including a CHECK constraint in the Credit Limit column:

ALTER TABLE dbo.Customers
ADD CONSTRAINT CK_Credit_Limit
CHECK (State='LA' AND [Credit Limit] <= 150000)
GO

INSERT INTO Customers (Customer_Id, Name, Status, Phone, State, Zip, Email, [Credit Limit])
VALUES (1, 'James Black', 'Mr', 5558787, 'LA', 46853, 'james@mail.com', 120000);
GO

INSERT INTO Customers (Customer_Id, Name, Status, Phone, State, Zip, Email, [Credit Limit])
VALUES (2, 'Mark Spencer', 'Mr', 3332244, 'NY', 23487, 'mark@mail.com', 200000);
GO

Let us examine the query in more detail. The CHECK constraint for the State column only accepts values of 'LA'. At the same time, the Credit column's values cannot exceed 150000.

Similarly, the CHECK constraint prevents other state codes from being written in the column.

As a result, the condition must be altered. According to the bank's business reasoning, Louisiana citizens are entitled to $150000 of the credit limit. This value, however, may differ for other residents.

We'll utilize the CASE clause inside the CHECK constraint to execute this case:

ALTER TABLE dbo.Customers
ADD CONSTRAINT CK_Credit_Limit
CHECK (CASE WHEN State='LA' AND [Credit Limit] <= 150000 THEN 1 ELSE 0 END = 1)
GO

This expression completely meets the business logic.

Example 2: I'd want to add a check constraint to a column that states that if the entered value for the column is more than 3, it should be saved as 1, otherwise the entered value should be stored as 0. For that instance, I'm using the query below, however it throws an error when I try to enter data.

Step 1: Create table

create table tblTestCheckConstraint 
(
id int,
NewColumn int
)

Step 2: alter table tblTestCheckConstraint

add constraint chk_tblTestCheckConstraint_NewColumn1 CHECK  
(
    CASE
        WHEN NewColumn >4 THEN 1
        ELSE NewColumn
    END = 1
)

Step 3: Insert Statement:

insert into tblTestCheckConstraint values ( 1,5)
insert into tblTestCheckConstraint values ( 1,2)

Error Message:

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "chk_tblTestCheckConstraint_NewColumn1". The conflict occurred in database "DBName", table "dbo.tblTestCheckConstraint", column 'NewColumn'.

The statement has been terminated.

Check restrictions are only useful for ensuring that data falls inside a set of criteria. The CHECK(...) clause is effectively a boolean condition: if the result is 1, the row is inserted; if the output is 0, the record is not entered, and the client receives an error.

I generated your table with your suggested verify constraint and numerous rows of test data to test this:

CREATE TABLE dbo.tblTestCheckConstraint /* always specify the schema */
(
id INT,
NewColumn INT
);

ALTER TABLE tblTestCheckConstraint
ADD CONSTRAINT chk_tblTestCheckConstraint_NewColumn1 CHECK  
(
    CASE
        WHEN NewColumn >4 THEN 1
        ELSE NewColumn
    END = 1
);

INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (1);
INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (2);
INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (3);
INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (5);

SELECT * FROM dbo.tblTestCheckConstraint;

This results in several errors and allows two rows to be inserted:

When intending to insert 2 and 3 (or anything other than 1 or >4), the constraint will return false, signalling to SQL Server that the insert will not be performed, and the error messages will be displayed.

Here's an instance of how you could do it with a trigger:

USE tempdb

CREATE TABLE dbo.tblTestCheckConstraint 
(
id INT CONSTRAINT PK_tblTestCheckConstring PRIMARY KEY CLUSTERED IDENTITY(1,1),
NewColumn INT NULL
);
GO

CREATE TRIGGER dbo.trg on dbo.tblTestCheckConstraint
INSTEAD OF INSERT
AS 
BEGIN
    INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
    SELECT NewColumn
    FROM INSERTED i
    WHERE i.NewColumn <=3 OR i.NewColumn IS NULL;

    INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
    SELECT 1
    FROM INSERTED i
    WHERE i.NewColumn >3;
END;
GO
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (1);
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (2);
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (3);
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (4);

SELECT *
FROM dbo.tblTestCheckConstraint;

Before trying to insert data, make sure the constraint is removed from the table because it might clash with the trigger code:

ALTER TABLE dbo.tblTestCheckConstraint
DROP CONSTRAINT chk_tblTestCheckConstraint_NewColumn1;

SQL Check Constraint Age

The user specifies a check constraint while building the table, or it is introduced when the table is changed.

It can either be applied to a single attribute or to the entire table. It assists us in ensuring that the value we are putting into our database meets the necessary criteria.

1. Create a table with Check Constraint

Syntax:

CREATE TABLE tableName (
colName1 datatype,
colName2 datatype CHECK(expression),
colName3 datatype,
…….
);

2. Alter a table with Check Constraint

Syntax :

ALTER TABLE <TableName>
ADD    Constraint <Constraint_Name>
CHECK (<Check_Constraint_Condition>)

Example 1: Imagine an employee's age as an integer data type for a column in a table of employees. In the Age column, one of the employees has written -76 (negative 76). It is impossible to have a negative number in the context of age. Because negative values are allowed in an integer data type, SQL server will not raise an error. To avoid this, only positive numbers should be accepted in the Age column. This can be performed by adding a check constraint to the employee table's Age column. Let's get going.

Step 1: Create an Employee Table with CHECK Constraints

Employees at firm ABC must be 18 years old or older, according to its standards. As a result, when constructing the employee table, we'll add a check constraint to ensure that the employees' ages are always more than or equal to 18.

IF OBJECT_ID (N'Employee' , N'U' ) IS NOT NULL
DROP TABLE Employee;

CREATE TABLE Employee
(
EmployeeId INT PRIMARY KEY ,
Salary Numeric( 18,2 ),
Gender Char( 1),
Age    INT CHECK  (AGE >= 18) --Check Constraint Condition
);

The condition CHECK(AGE >=18) adds a check constraint to the employee database, specifying that an employee's age must be greater than or equal to 18.

Step 2: Verify our check constraint by inserting some records.

a. Add an employee whose age is 18.

INSERT INTO Employee   (EmployeeId, Name, Salary , Gender, Age)   
Values ( 100, 'Niraj', 67000.00 , 'M' , 18);

The age has been successfully inserted.

b. Add a 15-year-old employee, who is beyond the check constraint range.

INSERT INTO Employee (EmployeeId, Name, Salary , Gender, Age)
Values(101,'Chetan',56000.00,'M',15);

Because Age = 15 is beyond the CHECK constraint's range, SQL Server has raised the error below.

Msg 547, Level 16, State 0, Line 13

The INSERT statement conflicted with the CHECK constraint "CK__Employee__Age__3B0BC30C". The conflict occurred in database "VishLearningDB", table "dbo.Employee", column 'Age'.

The statement has been terminated.

c. Add an employee whose age is 26.

INSERT INTO Employee (EmployeeId, Name, Salary , Gender, Age)
Values(103,'Mani',329999.00,'F',15); 

The value has successfully been inserted.

Step 3: Add a CHECK Constraint on an Existing Table

Now we'll double-check that our Gender column only accepts M (male) and F (female) values.

We will create a check constraint.

Example :

ALTER TABLE Employee
ADD    Constraint CK_Employee_Gender
CHECK (Gender = 'M' OR GENDER = 'F' );

Let’s insert one more male and female employee.

INSERT INTO Employee ( EmployeeId, Name , Salary , Gender, Age )
Values(104 ,'Meera', 23000.00,'F' ,26);

INSERT INTO Employee ( EmployeeId, Name , Salary , Gender, Age )
Values(105 ,'Shailesh', 24000.00,'M' ,28);

Now, we will try inserting another character into the Gender column.

INSERT INTO Employee ( EmployeeId, Name , Salary , Gender, Age )
Values(105 ,'Vish', 24000.00,'V' ,28);

The check constraint has the error below.

Msg 547, Level 16, State 0, Line 31
The INSERT statement conflicted with the CHECK constraint "CK_Employee_Gender".
The conflict occurred in database "master", table "dbo.Employee", column 'Gender'. The statement has been terminated.

Example 2: Let's try inserting data into a new database called DataFlair_employees, which has a check for employee age.

Step 1: Create the table with Check constraint.

CREATE TABLE DataFlair_employees  (
Name varchar(50),
Age int CHECK(Age>=18),
Experience int );

Step 2: Insert data into the table.

INSERT INTO DataFlair_employees VALUES
('Rajni',28,5),
('Shyam',25,3),
('Nidhi',22,1),
('John',21,0),
('Anita',29,5),
('Raj',17,0);

Step 3: View the table.

Select * From DataFlair_employees;

Example 3: When building a new table, let's add a CHECK constraint to a table column.

Step 1: We establish a CHECK constraint on the Age column in the following create table statement, ensuring that the value for the Age column must be larger than 18.

CREATE TABLE dbo.Student
(
StudId INT NOT NULL IDENTITY(1,1), 
NAME VARCHAR(100), 
Age INT CHECK (Age > 18), 
Grade VARCHAR(5)
)

OR

CREATE TABLE dbo.Student
(
StudId INT NOT NULL IDENTITY(1,1), 
NAME VARCHAR(100), 
Age INT , 
Grade VARCHAR(5), 
CONSTRAINT Check_Student_Age CHECK(Age > 18)
)

As you can see, the orders were successfully executed. Let's see if the CHECK constraint has been added to the table. Sp_help Stored procedure can be used.

Step 2: Execution

Lets Execute the Sp_help Stored Procedure.

EXEC Sp_help Student

As you can see, a CHECK constraint has been added to the Age column, with the name Check_Student_Age.

You can also use Object Explorer to see if the constraint is created or not, as illustrated below.

As you can see, it only shows the constraint name. If you want to get more information about Check_Student_Age, double-click on its name. A new query editor window will appear, displaying the table in design mode and a check constraint dialogue box with details on the constraints.

Step 3: Insert values into CHECK constraint Column

Let's see what occurs if the Age column value is more than 18 when inserting a student table.

INSERT INTO dbo.Student
(Name, Age, Grade)
VALUES
('Rakesh Agarwal', 20, 'C')

You can see, records inserted successfully.

Step 4: Display Records

Let's look at a record in the Student table, and you can see that it was successfully inserted. Because the value for the Age column is more than 18, the CHECK condition was satisfied, and records were successfully inserted.

SELECT * FROM dbo.Student

Lets try to insert record in student table for Age less than 18.

INSERT INTO dbo.Student
(Name, Age, Grade)
VALUES
('Mark Anthony', 17, 'B')

And you can see, this time CHECK constraint returns an error as value is less than18 which does not satisfy the CHECK condition (Age >18). Therefore It returns an error.

Step 5: UPDATE values into CHECK constraint column

The allowing to the column during the update are likewise validated by the CHECK constraint.

The following sentence attempts to change the value of the Age column from 20 to 15, which is less than 18.

As you can see, the Age value for StudId =1 is 20. We'll now change the Age value to 15.

Lets update Age value for StudId =1 from 20 to 15.

Update student SET StudAge =10 where StudID=1

As you can see, the CHECK constraint prevents you from updating a Age that is less than 18.

SELECT * FROM dbo.Student

SQL Check Constraint Date Greater Than

The CHECK constraint restricts the values that can be entered in one or more table columns. The CHECK requirement ensures that the column's data is always correct.

When using logical expressions that return TRUE or FALSE, the CHECK constraint is used.

Example 1: Let’s take a look at the batches table:

SQL CHECK constraint - batches table

We can use a CHECK constraint in the batches table to ensure that the end date is always greater than or equal to the begin date, and that the starting date is greater than or equal to 1970-01-01.

The following is a logical expression that shows the logic:

enddate >= begindate AND begindate >= '1970-01-01'

The database engine rejects the modification and issues an error if you try to enter a begin date that is less than or equal to the end date, or if the start date is less than 1970-01-01.

A single CHECK constraint can be applied to many columns in a table, or multiple CHECK constraints can be applied to multiple columns in a table.

In terms of regulating the values that can be entered in the column, a CHECK constraint is similar to a FOREIGN KEY constraint. The CHECK constraint employs a logical expression to determine valid values, whereas the FOREIGN KEY constraint takes values from another table's column to establish constant values.

Creating CHECK constraint examples

We can define a CHECK constraint when creating a table as follows:

CREATE TABLE batches (
  batchid int(11) NOT NULL,
  batchname varchar(255) NOT NULL,
  begindate date NOT NULL,
  enddate date NOT NULL,
  courseid int(11) NOT NULL,
  PRIMARY KEY (batchid),
  CONSTRAINT fk_courseid FOREIGN KEY (courseid) REFERENCES courses (courseid),
  CONSTRAINT chk_date CHECK(enddate >= begindate AND begindate >= '1970-01-01')
)

The following sentence was used to define a CHECK constraint in the query above:

CONSTRAINT chk_date CHECK(enddate >= begindate AND begindate >= '1970-01-01')

The CHECK constraint is called chk_date, and the logical expression for determining valid values for the begin date and end date column columns is called

enddate >= begindate AND begindate >= '1970-01-01'

You can use the ALTER TABLE command to add a CHECK constraint to an existing table:

ALTER TABLE batches
ADD CONSTRAINT chk_date 
CHECK(enddate >= begindate AND begindate >= '1970-01-01')

Example 2: In the example below, we want to make sure that the next census date is later than the previous one. We'll utilise the CHECK(LastCensusNextCensus) phrase in the table creation query to establish this data validation rule.

CREATE TABLE CountryListCensus (
    Id INT IDENTITY PRIMARY KEY,
    CountryName VARCHAR(255) NOT NULL,
    CountryPopulation INT CHECK(CountryPopulation > 0),
    LastCensus DATE,
    NextCensus DATE,
    CHECK(LastCensus<NextCensus)
)

Now we want to add a row to the CountryListCensus database, but with the same values in the LastCensus and LastCensus columns. Our query will return an error in this situation.

INSERT INTO CountryListCensus VALUES('PeaceCountry',10000,
    '20210101','20210101')

Example 3: When you use the CREATE TABLE statement to create a table, you mostly use the CHECK constraint.

The following statement defines an employees table.

Step 1:

DROP TABLE IF EXISTS employees;

Step 2: Create table

CREATE TABLE employees (
	id SERIAL PRIMARY KEY,
	first_name VARCHAR (50),
	last_name VARCHAR (50),
	birth_date DATE CHECK (birth_date > '1900-01-01'),
	joined_date DATE CHECK (joined_date > birth_date),
	salary numeric CHECK(salary > 0)
);

The employees table has three CHECK constraints:

  • To start, the employee's birthday (birth_date) must be later than January 1, 1900. If you try to enter a date before January 1, 1900, you'll get an error message.
  • The joined date (joined_date) must also be larger than the birth date (birth_date). This check will prevent incorrect dates from being updated in terms of semantic significance.
  • Third, and most obviously, the pay must be larger than zero.

Step 3: Let’s try to insert a new row into the employees table:

INSERT INTO employees (first_name, last_name, birth_date, joined_date, salary)
VALUES ('John', 'Doe', '1972-01-01', '2015-07-01', - 100000);

In the pay column, the statement intended to insert a negative salary. PostgreSQL, on the other hand, returned the following error message:

[Err] ERROR: new row for relation "employees" violates check constraint "employees_salary_check"
DETAIL: Failing row contains (1, John, Doe, 1972-01-01, 2015-07-01, -100000).

The insert failed because the salary column has a CHECK constraint that only permits positive values.

PostgreSQL names the CHECK constraint by default using the pattern below:

{table}_{column}_check

For example, the constraint on the salary column has the following constraint name:

employees_salary_check

Step 4: If you wish to give a CHECK constraint a specific name, you can do so after the CONSTRAINT phrase, as seen below:

column_name data_type CONSTRAINT constraint_name CHECK(...)

See the following example:

salary numeric CONSTRAINT positive_salary CHECK(salary > 0)

Step 5: Define PostgreSQL CHECK constraints for existing tables

The ALTER TABLE statement is used to apply CHECK constraints to existing tables. Assume you already have a prices_list table in your database.

CREATE TABLE prices_list (
	id serial PRIMARY KEY,
	product_id INT NOT NULL,
	price NUMERIC NOT NULL,
	discount NUMERIC NOT NULL,
	valid_from DATE NOT NULL,
	valid_to DATE NOT NULL
);

The CHECK constraints can now be added to the prices list table with the ALTER TABLE query. Both the price and the discount must be more than zero, with the discount being lower than the price. The AND operators are used in a Boolean expression.

ALTER TABLE prices_list 
ADD CONSTRAINT price_discount_check 
CHECK (
	price > 0
	AND discount >= 0
	AND price > discount
);

The valid to date ( valid_to) must be greater than or equal to valid from date ( valid_from).

ALTER TABLE prices_list 
ADD CONSTRAINT valid_range_check 
CHECK (valid_to >= valid_from);

The CHECK constraints can be used to provide further logic to the database layer to limit the values that the columns can allow. You may ensure that data is appropriately updated to the database by using the CHECK constraint.


SQL Check Constraint Disable

Disabling constraint prevents Constraint from running.

The syntax for disabling a check constraint :

ALTER TABLE table_name
NOCHECK CONSTRAINT constraint_name;
  • table_name: The name of the table for which the check constraint should be removed.
  • constraint_name: The name of the check constraint to disable.

Example 1: Let's look at how to eliminate a check constraint in SQL Server using an instance.

ALTER TABLE employees
NOCHECK CONSTRAINT check_salary;

In this SQL Server example, we'll disable the check_salary constraint on the employees table.

Example 2: Disable the CHECK Constraint

Step 1: Use the NOCHECK parameter in an ALTER TABLE statement to deactivate a CHECK constraint.

ALTER TABLE Occupation  
NOCHECK CONSTRAINT chkJobTitle; 

This code disables a constraint called chkJobTitle.

Step 2: Review the CHECK Constraint

To ensure that our constraint has been disabled, we can use the sys.check constraints system view:

SELECT 
  name,
  is_disabled,
  is_not_trusted,
  definition
FROM sys.check_constraints;

Output:

+-----------------+---------------+------------------+----------------------------------------+
| name            | is_disabled   | is_not_trusted   | definition                             |
|-----------------+---------------+------------------+----------------------------------------|
| chkPrice        | 0             | 0                | ([Price]>(0))                          |
| chkValidEndDate | 0             | 0                | ([EndDate]>=[StartDate])               |
| chkTeamSize     | 0             | 0                | ([TeamSize]>=(5) AND [TeamSize]<=(20)) |
| chkJobTitle     | 1             | 1                | ([JobTitle]<>'Digital Nomad')          |
+-----------------+---------------+------------------+----------------------------------------+

I choose all CHECK constraints from the current database in this example.

Because the is disabled column is set to 1, we can see that this is the only one that's disabled.

The is_not_trusted column has also been set to 1. This indicates that the system has not tested the CHECK constraint for all rows.

To put it another way, we can't assume that the constraint has checked all of the data. Because the constraint has been disabled, data can now enter the database without being checked by the constraint. As a result, there's a chance that the database contains invalid data.

If you ever need to re-enable the CHECK constraint, you can do so by restoring its trust (by using the WITH CHECK option). Before the constraint is enabled, it will check all existing rows.

For an example of how trust is changed depending on how you re-enable the constraint, see What You Should Know about WITH NOCHECK when Enabling a CHECK Constraint in SQL Server.


SQL Check Constraint Drop

Dropping the constraint will cause the table to vanish.

Syntax:

The syntax for dropping a check constraint :

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
  • table_name: The name of the table for which the check constraint should be removed.
  • constraint_name: Remove the check constraint's name.

Example 1: The sys.check constraints system view can be used to eliminate SQL Check Constraints when the constraint's name is unknown. This SQL Server article demonstrates how to design a sql procedure that removes a check constraint using only the table and column names as arguments.

Database administrators and T-SQL programmers use SQL Server Check Constraint to apply a validation check defined at check constraint expression on inserted or changed column data.

Let's continue our discussion by creating a sample case for the purpose of the SQL lesson. Here is the Create script for a table with a SQL Check Constraint declared in a column. The sql check constraint ensures that the data submitted for the CustomerGroup column matches one of the values in the specified list.

Step 1: Create Table

CREATE TABLE Customer
(
 CustomerId int identity(1,1) not null,
 Title nvarchar(1000) not null,
 SectorId smallint,
 CustomerGroup char(2) check ( CustomerGroup IN ('AA','BB','EE') ),
-- more columns ....
)

Step 2: SQL constraint works with Insert statements.

Insert Into Customer (Title, CustomerGroup) Values ('Kodyaz SQL','AA')
Insert Into Customer (Title, CustomerGroup) Values ('Kodyaz SAP',NULL)
Insert Into Customer (Title, CustomerGroup) Values ('Kodyaz WP8','WP')

The first two INSERT INTO queries succeed despite the fact that the CustomGroup column insert value in the second SQL Insert statement is NULL. SQL Server Check Constraint in SQL statements does not throw an exception in the NULL scenario. Last SQL Insert Into command with column value, on the other hand, does not validate SQL check constraint specified on that table column.

The SQL Check constraint name is CK_Customer_Custom_71D1E811, according to this SQL Server exception notice. The database name, as well as the table and column names where the SQL check constraint is specified, are all listed in the error information.

Step 3: -- for example

ALTER TABLE Customer DROP Constraint CK__Customer__Custom__71D1E811

After removing the SQL Check Constraint, you can now put data into the database table that was previously causing an issue in this SQL tutorial.

Database developers and SQL Server administrators, on the other hand, don't always have it easy. SQL experts should be able to drop constraints programmatically even if they don't know the name of the SQL Server check constraint.

The sys.check constraints system view in SQL Server comes in handy here. sys.check_constraints The list of check constraints defined on the database table's columns is returned by SQL view.

Step 4: Let's execute a SELECT query on sys.check_constraints and see what does it display.

SELECT * FROM sys.check_constraints

From the example check_constraints system view has the name schema_id, parent_object_id, parent_column_id and name columns.

Check constraints in SQL Server The sys.check constraints system view contains all of the information needed to eliminate and rebuild a SQL constraint.

Using the table name and column name, SQL programmers can write a stored procedure to discover the name of the check constraint. Then you can use the constraint name in the ALTER Table DROP Constraint command syntax.

Step 5: Here is a stored procedure which drop check constraint that the sql constraint name is unknown but the table and column name is known.

ALTER PROC SP_DROP_Check_Constraint (
 @table_name sysname,
 @column_name sysname
)
AS

declare @check_constraint_name sysname, @sql nvarchar(max)

select @check_constraint_name = name from sys.check_constraints
where
 parent_object_id = OBJECT_ID(@table_name) -- Table name
 AND type = 'C' -- Check Constraint
 AND parent_column_id = (
  select column_id from sys.columns
  where
   object_id = OBJECT_ID(@table_name)
   and name = @column_name -- Column name
 )

if @check_constraint_name is not null
begin
 SET @sql = N'ALTER TABLE ' + @table_name + ' DROP Constraint ' + @check_constraint_name
-- print @sql
 EXEC sp_executesql @sql
end
else
 throw 50001, 'SQL Server Check Constraint does not exist', 1

Step 6: Developers can now use the above SQL stored procedure to remove a check constraint whose name is specified in code using the table and column names. Let's look at how the Drop Check Constraint sql procedure works.

SP_DROP_Check_Constraint 'Employee', 'DateofEntry'

The stored procedure will throw the error indicated below if there is no such SQL Server check constraint. After deleting the SQL constraint, you can test it again by executing the stored procedure.

Msg 50001, Level 16, State 1, Procedure SP_DROP_Check_Constraint, Line 27
SQL Server Check Constraint does not exist

A related tutorial for developers will teach how to drop Default Constraint on a Table Column without knowing the name of the linked constraint.

Example 2: Let's look at an example of how to drop a check constraint in SQL Server.

ALTER TABLE employees
DROP CONSTRAINT check_last_name;

In this SQL Server example, we are dropping a check constraint on the employees table called check_last_name.


SQL Check Constraint Gender

Example 1: Let's make another constraint scenario. This time, I'd like to add a Gender Column Check Constraint. Male or Female should be the value for the Gender column.

CREATE TABLE Customer
(EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT,
Gender VARCHAR(10),
Salary FLOAT,
CONSTRAINT gender CHECK(Gender=’Male’ 18 AND Gender=’Female’)
)

Try to insert the record with Gender value other than Male and Female.

INSERT INTO Employee (EmployeeID,FirstName,LastName,Age,Gender,Salary) VALUES (1,'Arvind','Kumar',50,'Other',10000)

You will get the following error after running above query.

Example 2: The use of information restrictions and how they work are demonstrated in the following example. This short table offers information about the age and gender of applicants:

Step 1: Create Table

CREATE TABLE APPLICANTS
   (
   AP_NO	INT NOT NULL,
	GENDER 	CHAR(1) NOT NULL,
	CONSTRAINT GENDEROK
	  CHECK (GENDER IN ('M', 'F'))
	  NOT ENFORCED
	  ENABLE QUERY OPTIMIZATION,
    AGE	 INT NOT NULL,
	 CONSTRAINT AGEOK
	 CHECK (AGE BETWEEN 1 AND 80)
	 NOT ENFORCED
	 ENABLE QUERY OPTIMIZATION,
     );

Two options affect the behaviour of the column restrictions in this example. The first choice, NOT ENFORCED, tells the database management not to make sure this field is checked when data is entered or modified. This option can be TRUSTED or NOT TRUSTED, depending on your preferences. The database manager can trust that the data will adhere to the constraint if the informational constraint is set to TRUSTED. This is the standard setting. If NOT TRUSTED is defined, the database management is aware that most, but not all, data will violate the constraint. Because the choice of trusted or not trusted was not mentioned in this example, the option is NOT ENFORCED TRUSTED by default.

When SELECT statements are run against this table, the database management uses ENABLE QUERY OPTIMIZATION as the second option. When this value is supplied, the database manager will optimise the SQL using the constraints' data.

Step 2: The behaviour of insert statements may appear strange if the table contains the NOT ENFORCED option.

When executed against the APPLICANTS table, the following SQL generates no errors:

INSERT INTO APPLICANTS VALUES
	    (1, 'M'),
	    (2, 'F'),
	    (3, 'M'),
	    (4, 'F'),
	    (5, 'C'),
	    (6, 'S',100),

Applicant number five has the gender (C), which stands for child, whereas applicant number six has an unusual gender and is older than the AGE column's age limitations. Because the constraints are NOT ENFORCED and TRUSTED in both situations, the database manager will permit the insert to happen.

Step 3: The result of a select statement against the table is shown in the following example:

SELECT * FROM APPLICANTS
WHERE GENDER = 'C';

Output:

APPLICANT  GENDER  AGE
---------  ------  ---
 
0 record(s) selected.

Despite the fact that the value 'C' exists in the table, the database manager returned an erroneous answer to the query because the constraint on this column notifies the database manager that the only valid values are 'M' or 'F'. The database management could also use this constraint information when optimising the statement thanks to the ENABLE QUERY OPTIMIZATION keyword.

Step 4: As illustrated in the following example, the constraint must be adjusted using the ALTER TABLE statement.

ALTER TABLE APPLICANTS
ALTER CHECK AGEOK DISABLE QUERY OPTIMIZATION

If the query is reissued, the database manager will return the following correct results:

SELECT * FROM APPLICANTS
WHERE SEC = 'C';

Output:

APPLICANT	GENDER  AGE
---------  ------  ---
        5   C       10
    
1 record(s) selected.

Note: If the constraint attributes NOT ENFORCED NOT TRUSTED and ENABLE QUERY OPTIMIZATION for the table APPLICANTS had been provided from the beginning, the right results would have been returned after the first SELECT statement.

When you can assure that the application software is the only one inserting and modifying data, using NOT ENFORCED TRUSTED informational constraints is the ideal option. If the application already validates all of the information (such as gender and age in the previous example), applying informational restrictions can result in faster performance and less work. Data warehouse design is another application of informational limitations. You can also set the restrictions to NOT ENFORCED and NOT TRUSTED if you can't ensure that the data in the table will always respect to the constraint. When precise matching between the values in the foreign keys and the primary keys is not required, this form of restriction can be utilised. This limitation can still be used as part of a statistical view, allowing some SQL queries to be optimised.


SQL Check Constraint List of Values

CHECK constraint obtains valid values from a logical expression, while foreign key constraint takes valid values from a separate table.

CHECK constraints are related to foreign key constraints in that they both validate data that goes to a column. The difference is that foreign key constraints acquire valid values from another table, whereas CHECK constraints get valid values from a logical expression.

If you're new to databases, you can learn more about limitations like check, foreign key, and others by clicking on this link.

As an example, transform a simple lookup table foreign key to a main table check constraint.

Syntax: sql constraint check value in list

ALTER TABLE < table>
ADD CONSTRAINT chk_val CHECK (col in ('yes','no','maybe'))

Example 1: sql constraint check value in list:

CREATE TABLE test(
    _id BIGINT PRIMARY KEY NOT NULL,
    decision NVARCHAR(5),
    CHECK (decision in ('yes','no','maybe'))
);

Example 2: The advantages and disadvantages of employing a check constraint instead of a foreign key constraint to ensure referential integrity between two tables.

Step 1: To create a check constraint on a new table:

CREATE TABLE table1 
  ( 
     col1 INT, 
     col2 VARCHAR(20), 
     CONSTRAINT chk_col1 check (col1>0) 
  ); 
go

Step 2: To add check constraint on existing table

ALTER TABLE table1 
  ADD CONSTRAINT chk_col1 check (col1>0); 
go

Step 3: To see our constraint is created properly we could check as

SELECT * 
FROM   sys.check_constraints 
WHERE  parent_object_id = Object_id('dbo.table1');
CHECK Constraints Step0

Let's say we want to construct a database application in which we need to verify that the state and zip code combination are correct before putting data into our table. We must first develop a lookup table to store the correct zip and state combination, followed by a scalar function to authenticate those zip codes. Then, as shown below, we may establish our customers table with a CHECK constraint:

--create a lookup table

create table tStateList(
	[State] char(2),
	ZIPCodeRange char(11),
	MinVal int,
	MaxVal int
);
go

--insert zip code values
INSERT INTO tStateList ([State],ZIPCodeRange,MinVal,MaxVal)
    VALUES        ('AK','99500-99999','99500','99999'),
		  ('AL','35000-36999','35000','36999'),
		  ('AR','71600-72999','71600','72999'),
		  ('AZ','58000-86599','58000','86599'),
		  ('CA','90000-96199','90000','96199'),
		  ('CO','80000-81699','80000','81699'),
		  ('CT','06800-06999','06800','06999'),
		  ('DC','20001-20599','20001','20599'),
		  ('DE','19700-19999','19700','19999'),
		  ('FL','32100-34999','32100','34999'),
		  ('GA','30000-31999','30000','31999'),
		  ('HI','96700-96899','96700','96899'),
		  ('IA','50000-52899','50000','52899'),
		  ('ID','83200-83899','83200','83899'),
		  ('IL','60000-62999','60000','62999'),
		  ('MS','38600-39599','38600','39599'),
		  ('MT','59000-59999','59000','59999'),
		  ('NC','27000-28999','27000','28999'),
		  ('ND','58000-58899','58000','58899'),
		  ('UT','84000-84799','84000','84799');
go

Now let us create a scalar function that will help us validate zip codes and state combination.

--create a scalar funcunction that validate zipCodes

if object_id('fnCheckZipCode','FN') is not null
	drop function fnCheckZipCode;
go

create function fnCheckZipCode(@state char(2), @zip int)
returns bit
as
begin
	declare @zipExists bit=0;
	if exists(select * from tStateList 
                  where [State] = @state and @zip between MinVal and MaxVal)	
		set @zipExists = 1;
	else
		set @zipExists = 0;
	return @zipExists;
end
go

--sample function call

--select dbo.fnCheckZipCode( 'co', 90000)

Now let us create our customer table with zipCode validation CHECK constraint:

--==============================================
--Create our table and test our CHECK constraint
--==============================================

if object_id('customers','U') is not null
	drop table customers;
create table customers(
	customerid int not null identity(1,1),
	firtName varchar(30),
	lastName varchar(30),
	[state]  char(2),
	zipCode  int,
	CONSTRAINT chkZipCode CHECK (dbo.fnCheckZipCode([state],zipCode)=1)
);
 
--first attempt with correct state and zipCode combination

insert into customers values('John','Doe','CO',80202);

CHECK Constraints Step1

--second attempt with a wrong state and zipCode combination

insert into customers values('Jane','Doe','CA',11594);
CHECK Constraints Step2

This way we manage to create a data validation on customers table using CHECK constraint.

Example 3: Setup SQL Server Check Constraint Example:

Here is the T-SQL for a typical foreign key relationship.

create table lookup_fk (id int not null, description varchar(10));
create table main_fk (id int not null identity (1, 1), lookupid int, col1 int,col2 int,col3 varchar(100));

alter table lookup_fk add constraint pk_lookup primary key clustered (id);
alter table main_fk add constraint pk_main primary key clustered (id);
alter table main_fk add constraint fk_main_lookup foreign key 
      (lookupid) REFERENCES lookup_fk (id) on update cascade on delete cascade;

create index ix_main_fk_lookupid on main_fk (lookupid);

Using the following T-SQL, we may recreate this table structure using a check constraint instead of a foreign key constraint. We must define the collection of values that make up the check constraint with the check constraint. As you'll see later, this would have to be modified on the fly, but for this test, we'll merely supply the check constraint ahead of time.

create table lookup_cc (id int not null, description varchar(10));
create table main_cc (id int not null  identity (1, 1), lookupid int, col1 int,col2 int,col3 varchar(100));

alter table lookup_cc add constraint pk_lookup_cc primary key clustered (id);
alter table main_cc add constraint pk_main_cc primary key clustered (id);
alter table main_cc add constraint ck_main_cc_lookupid check 
      ((lookupid IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19)));

create index ix_main_cc_lookupid on main_cc (lookupid);

1. Load Test Data to SQL Server Tables

We can now load some test data into our table structure that we've developed. The T-SQL to load both the main and lookup tables is shown below.

declare @cnt integer
declare @incnt integer
select @cnt=1
while @cnt < 20
begin
    insert into lookup_fk values (@cnt,'count ' + cast(@cnt as varchar));
    insert into lookup_cc values (@cnt,'count ' + cast(@cnt as varchar));
    select @incnt=1
while @incnt < 100
begin 
   insert into main_fk (lookupid,col1,col2,col3) values                        
               (@cnt,@cnt,@cnt,'dummydatadummydatadummydatadummydata' + cast(@cnt as varchar));
   insert into main_cc (lookupid,col1,col2,col3) values
               (@cnt,@cnt,@cnt,'dummydatadummydatadummydatadummydata' + cast(@cnt as varchar));
   select @incnt=@incnt+1
end
select @cnt=@cnt+1
end

2. SQL Server Referential Integrity Test

The first thing we must check is that our referential integrity is still intact. Our lookup table is loaded with id values up to 19, so let's attempt inserting a value into the main database with a lookup value greater than 19.

insert into main_fk (lookupid,col1,col2,col3) values 
       (20,15,15,'dummydatadummydatadummydatadummydata' + cast(15 as varchar));

insert into main_cc (lookupid,col1,col2,col3) values 
       (20,15,15,'dummydatadummydatadummydatadummydata' + cast(15 as varchar));

We can see that after running the aforementioned T-SQL, we obtain a similar error message to the foreign key example, and that no records were added into our table.

Msg 547, Level 16, State 0, Line 40
The INSERT statement conflicted with the FOREIGN KEY constraint "fk_main_lookup".
The conflict occurred in database "test", table "dbo.lookup_fk", column 'id'.

Msg 547, Level 16, State 0, Line 41
The INSERT statement conflicted with the CHECK constraint "ck_main_cc_lookupid".
The conflict occurred in database "test", table "dbo.main_cc", column 'lookupid'.

3. SQL Server Insert Performance Test

We can investigate the performance now that we know the referential integrity is still working as planned. Let's start by testing insert performance against any table with a single insert command. Here's the T-SQL code.

insert into main_fk (lookupid,col1,col2,col3) values 
 (15,15,15,'dummydatadummydatadummydatadummydata' + cast(15 as varchar));

insert into main_cc (lookupid,col1,col2,col3) values 
  (15,15,15,'dummydatadummydatadummydatadummydata' + cast(15 as varchar));

We can observe in SQL Profiler that adding into the table with the check constraint requires less reads than inserting into the table with the foreign key since the lookup table is no longer queried.

You can also confirm this by running the query with'set statistics io on'. The second query against just reads from the main table, as you can see from the result below.

Table 'lookup_fk'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, 
 lob physical reads 0, lob read-ahead reads 0.
Table 'main_fk'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, 
      lob physical reads 0, lob read-ahead reads 0.
Table 'main_cc'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, 
      lob physical reads 0, lob read-ahead reads 0.

4. SQL Server Update Performance Test

Let's try another test using an update statement. In each of the primary tables, we can update the lookupid value for a single entry. Here is the T-SQL for these two statements.

update main_fk set lookupid=10 where id=231;
update main_cc set lookupid=10 where id=231;

The update statement, like the insert statement, is marginally faster and performs fewer reads.

We can check that the extra reads of the lookup table cause the additional resources to be required by the foreign key structure query using the output from having'set statistics io on' activated.

Table 'lookup_fk'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, 
      lob physical reads 0, lob read-ahead reads 0.
Table 'main_fk'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, 
      lob physical reads 0, lob read-ahead reads 0.
Table 'main_cc'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, 
      lob physical reads 0, lob read-ahead reads 0.

5. SQL Server Delete Performance Test

Using our new check constraint structure, performing a remove is far more difficult than using a conventional foreign key. Because the cascade clause was used to construct the foreign key on the main_fk table, all we have to do to eliminate an entry from the lookup table (and thus the main_fk table) is use the delete command on the lookup table.

begin transaction;
   delete from lookup_fk where id=19;
commit transaction;

There's a lot more to take care of with the check constraint structure, since records will need to be manually removed from both tables, and the check constraint on the main cc table will need to be regenerated with the lookupid value removed. All of these instructions have DML and DDL attached to them.

begin transaction;
   delete from lookup_cc where id=19;
   delete from main_cc where lookupid=19;
   alter table main_cc drop constraint ck_main_cc_lookupid ;
   alter table main_cc with nocheck add constraint ck_main_cc_lookupid 
         check ((lookupid IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18)));
commit transaction;

Using SQL Profiler, we can see that, despite having to perform~10% more reads than the foreign key structure, the check constraint runs twice as fast and consumes half the CPU.

Note that RowCount does not include cascade deletes.


SQL Check Constraint Multiple Columns

For check constraints that refer to several columns, we can build data validation rules.

A check constraint can apply to more than one column in the same table when you use the multiple-column constraint format to specify it. (However, you cannot establish a check constraint whose condition uses a value from another table's column.)

Example 1: Multiple columns can be referenced by a CHECK constraint. For example, in the test.products table, you want to make sure that the discounted price is always lower than the standard price:

CREATE TABLE test.products(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2) CHECK(unit_price > 0),
    discounted_price DEC(10,2) CHECK(discounted_price > 0),
    CHECK(discounted_price < unit_price)
);
  • The first two unit price and discounted price limitations should look familiar.
  • The third constraint employs a novel syntax that is not tied to any specific column. Instead, it appears in the comma-separated column list as a separate line item.
  • Column constraints are the first two, whereas table constraints are the third.

Column restrictions can also be written as table constraints. Table constraints, on the other hand, cannot be written as column constraints. You may, for example, modify the preceding statement as follows:

CREATE TABLE test.products(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2),
    discounted_price DEC(10,2),
    CHECK(unit_price > 0),
    CHECK(discounted_price > 0),
    CHECK(discounted_price > unit_price)
);

or even:

CREATE TABLE test.products(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2),
    discounted_price DEC(10,2),
    CHECK(unit_price > 0),
    CHECK(discounted_price > 0 AND discounted_price > unit_price)
);

A table constraint can be given the same name as a column constraint:

CREATE TABLE test.products(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2),
    discounted_price DEC(10,2),
    CHECK(unit_price > 0),
    CHECK(discounted_price > 0),
    CONSTRAINT valid_prices CHECK(discounted_price > unit_price)
);

Add CHECK constraints to an existing table

The ALTER TABLE ADD CONSTRAINT statement is used to add a CHECK constraint to an existing table.

Suppose you have the following test.products table:

CREATE TABLE test.products(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2) NOT NULL
);

The following statement is used to add a CHECK constraint to the test.products table:

ALTER TABLE test.products
ADD CONSTRAINT positive_price CHECK(unit_price > 0);

To add a new column with a CHECK constraint, you use the following statement:

ALTER TABLE test.products
ADD discounted_price DEC(10,2)
CHECK(discounted_price > 0);

The following statement is used to create a valid_price CHECK constraint:

ALTER TABLE test.products
ADD CONSTRAINT valid_price 
CHECK(unit_price > discounted_price);

Example 2: This example compares two columns, acct1 and acct2, in the new table:

CREATE TABLE my_accounts 
   (
   chk_id   SERIAL PRIMARY KEY,
   acct1    MONEY,
   acct2    MONEY,
   CHECK (0 < acct1 AND acct1 < 99999),
   CHECK (0 < acct2 AND acct2 < 99999),
   CHECK (acct1 > acct2)
   )

In this example, the acct1 column must be greater than the acct2 column, or the insert or update fails.

Example 3: In the instance below, we want to make sure that the next census date is later than the previous one. We'll utilize the CHECK(LastCensus

CREATE TABLE CountryListCensus (
    Id INT IDENTITY PRIMARY KEY,
    CountryName VARCHAR(255) NOT NULL,
    CountryPopulation INT CHECK(CountryPopulation > 0),
    LastCensus DATE,
    NextCensus DATE,
    CHECK(LastCensus< NextCensus)
)

Now we want to add a row to the CountryListCensus database, but with the same values in the LastCensus and LastCensus columns. Our query will return an error in this situation.

INSERT INTO CountryListCensus VALUES('PeaceCountry',10000,
'20210101','20210101')

SQL Check Constraint Naming Convention

A Check Constraint is a criterion for a column that each row must meet.

The limitation can also be given a name. This makes it easy to remove the limitation of no longer being required in the future.

The naming convention for a check constraint is:

  • The check constraint should use the syntax “CHK_< TableName>_< ColumnName>.
  • Each Check Constraint name should have a “CHK_” prefix.
  • The first letter of both TableName and ColumnName should be capitalized.
  • TableName should be end with character "s" (or "es") to indicate plural.

Note: The names of the constraints must be unique across the database. Also, when naming the limitation, use proper naming convention.

Example 1:

CHK_Employees_EmpSalary
CHK_Employees_Age
CHK_OrderDetails_OrderPrice

Example 2: Here we use the CK_< TableName>_< ColumnsName> naming convention.

CREATE TABLE Employee (
    EmployeeID  int         ,
    Name       varchar(50)  ,
    Salary     Decimal(18,2) NOT NULL   ,
    Primary Key (EmployeeID),
    CONSTRAINT CK_Employee_Name   CHECK (len(Name) > 15),
    CONSTRAINT CK_Employee_Salary CHECK (Salary > 0), 
)