Add a CHECK constraint to the existing table

In this article, you’ll know how to add a CHECK constraint to the existing table.

CREATE TABLE ActiveCustomersSummary
(
SummaryID INT IDENTITY(1,1) PRIMARY KEY,
CustomerID INT NOT NULL,
CustomerName VARCHAR(50),
OrdersCount INT,
StartEffectiveDate DATE,
EndEffectiveDate DATE
);

Now, let’s ALTER the table and add a CHECK constraint.

ALTER TABLE ActiveCustomersSummary ADD CONSTRAINT ck_ActiveStatus_ActiveCustomersSummary CHECK
(
(StartEffectiveDate < GETDATE() AND EndEffectiveDate > GETDATE())
OR OrdersCount > 1
);

Let’s test it by adding a few rows.

INSERT INTO ActiveCustomersSummary VALUES
(1001,’Zafar’,10,’2022-10-01′,’2099-12-31′),
(1002,’Tahir’,5,’2022-11-01′,’2099-12-31′),
(1003,’Sanaa’,5,’2022-11-01′,’2099-12-31′);

SELECT * FROM ActiveCustomersSummary;

All three rows are successfully inserted because they are not violating the CHECK constraint. Let’s see what happens if we provide a row that violates the constraint.

INSERT INTO ActiveCustomersSummary VALUES
(1004,’Hadi’,0,‘2023-10-01’,’2099-12-31′);

SELECT * FROM ActiveCustomersSummary;

In accordance with the constraint, either StartEffectiveDate (which must be less than 2023-03-31) OR OrdersCount must be greater than 1. The aforementioned values broke the rule, so it was a failure. It throws the following error.

Try yourself.
http://sqlfiddle.com/#!18/8a06b1/4