This article demonstrates how to add a CHECK constraint to an existing table.

You can add a constraint to an existing table by using the ALTER TABLE statement along with the ADD CONSTRAINT argument. Examples below.

Example 1 – Create the Table

First, let’s create a table for which we’ll add the CHECK constraint.

CREATE TABLE Event
(
  EventId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  EventName varchar(255) NOT NULL,
  StartDate date NOT NULL,
  EndDate date NOT NULL,
  Price smallmoney NOT NULL
);

Example 2 – Add a Column-Level Constraint

Now let’s add a CHECK constraint to the Price column.

ALTER TABLE Event
  ADD CONSTRAINT chkPrice CHECK (Price > 0);

This constraint will ensure that the price is always greater than zero.

Now that the constraint has been added, here’s what happens if we try to insert invalid data:

INSERT INTO Event ( EventName, StartDate, EndDate, Price )
VALUES ( 'ICCC 2020', '2020-01-01', '2020-02-02', 0 );

Result:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "chkPrice". The conflict occurred in database "EMS", table "dbo.Event", column 'Price'.

In this case, the CHECK constraint specifies that all data in the Price column must be greater than 0. In other words, the price can’t be zero and it can’t be negative.

This is referred to as a column-level constraint, because it is defined on a single column. It applies to data in one column.

Example 3 – Add a Table-Level Constraint

Now let’s add a table-level CHECK constraint. This will check data in two columns.

ALTER TABLE Event
  ADD CONSTRAINT chkEndDate 
  CHECK (EndDate >= StartDate);

In this case I add a constraint to ensure that the end date can never be earlier than the start date. This is checking data across two columns and is therefore a table-level constraint.

Try to insert an invalid value:

INSERT INTO Event ( EventName, StartDate, EndDate, Price )
VALUES ( 'ICCC 2020', '2020-01-01', '1970-02-02', 150.00 );

Result:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "chkEndDate". The conflict occurred in database "EMS", table "dbo.Event".

As expected, the operation fails, because my end date is earlier than the start date.

Note that in order to test this constraint, I had to increase the price to a valid value in order to prevent the previous constraint from being triggered first (CHECK constraints are validated in the order they are created).

Example 4 – Successfully Inserting Data that Conforms with the Constraint

In order to successfully insert a row, all we need to do is make sure we’re inserting valid values.

Example:

INSERT INTO Event ( EventName, StartDate, EndDate, Price )
VALUES ( 'ICCC 2020', '2020-01-01', '2020-02-02', 150.00 );

SELECT * FROM Event;

Result:

+-----------+-------------+-------------+------------+----------+
| EventId   | EventName   | StartDate   | EndDate    | Price    |
|-----------+-------------+-------------+------------+----------|
| 4         | ICCC 2020   | 2020-01-01  | 2020-02-02 | 150.0000 |
+-----------+-------------+-------------+------------+----------+

Note that the EventId column has already incremented to 4. This is because it’s an IDENTITY column. An important thing to remember about IDENTITY columns is that they increment even when a constraint causes an INSERT operation to fail.

Some Restrictions of CHECK Constraints

Here are a few restrictions to be mindful of when working with CHECK constraints:

• The search condition must evaluate to a Boolean expression and cannot reference another table.
• The expression cannot contain alias data types.
• CHECK constraints cannot be defined on text, ntext, or image columns.