SQL Server › Constraints (Primary Key, Foreign Key, Check & Unique)

How would you add a new constraint to an existing table that already contains data, especially if some existing rows might violate that constraint?

Advanced

Answer

You first identify and fix any existing data that would violate the new constraint, then add the constraint using an ALTER TABLE statement, optionally using WITH NOCHECK if you need to add it without validating existing data immediately, though this leaves the constraint untrusted until it is explicitly checked later.

-- First, identify violating rows
SELECT * FROM Products WHERE Price <= 0;

-- Then add the constraint after cleaning up the data
ALTER TABLE Products
ADD CONSTRAINT CK_Price CHECK (Price > 0);

Real-world example A team cleans up a handful of products with incorrect zero dollar prices before successfully adding a check constraint that prevents this type of pricing error from happening again in the future.

Common follow-ups: What does it mean for a constraint to be untrusted in SQL Server?;How do you later validate an untrusted constraint against all existing data?

Data Types & Schema Design;Error Handling with TRY CATCH