Stop Blaming the User: Let Constraints Catch Bad Data

Every tech team has a favorite joke about users, and I have laughed at most of them. But blaming the user never fixed a single bad row. A table that refuses bad data does.

Why Blaming the User Feels Good

Blaming the user is quick, and the user is not in the room. But if one person could type 1900-01-01 as a birth date, the next person will too. A form that allows a mistake will collect that mistake every day, politely, forever.

I have cleaned up enough of these tables to know the pattern. The bad data was never a surprise. It was an invitation the table sent to everyone.

Let the Table Say No

Constraints are rules the table enforces on every insert and update, no matter which app, script or intern sends the data.

CREATE TABLE dbo.Member
(
    MemberId int IDENTITY(1,1) PRIMARY KEY,
    Email nvarchar(254) NOT NULL
        CONSTRAINT UQ_Member_Email UNIQUE,
    BirthDate date NULL
        CONSTRAINT CK_Member_BirthDate CHECK (BirthDate > '1900-01-01'),
    CountryCode char(2) NOT NULL
        CONSTRAINT CK_Member_CountryCode CHECK (CountryCode LIKE '[A-Z][A-Z]')
);

NOT NULL means the value must be there. UNIQUE stops the second account with the same email. CHECK keeps birth dates and country codes believable. A foreign key, where a table has a parent, stops rows that point to nothing.

Adding Rules to a Table That Already Has Bad Data

On an old table, the new rule will fail if bad rows are already inside. Find them first, fix or move them, and then add the constraint WITH CHECK so SQL Server checks every existing row and trusts the rule.

SELECT MemberId, BirthDate
FROM dbo.Member
WHERE BirthDate <= '1900-01-01';

ALTER TABLE dbo.Member WITH CHECK
    ADD CONSTRAINT CK_Member_BirthDate_Recent CHECK (BirthDate > '1900-01-01');

Adding it WITH NOCHECK skips the old rows. The rule then protects new data only, and SQL Server marks it as not trusted, so the optimizer cannot rely on it. Clean first, then check.

Give Friendly Messages at the Door

The application still checks the form first, because it can show a friendly message next to the right box. The database is the last guard at the door, for every path the form does not cover.

Name your constraints clearly. When CK_Member_BirthDate shows up in an error, a developer knows exactly which rule was broken without opening a single script.

Stop Blaming the User, Start Reading the Rejects

Log the constraint errors your application receives, and look at which ones fire most. If the birth date check fails fifty times a day, the form is confusing, and that is a design problem, not a user problem.

Fix the form, keep the constraint, and stop blaming the user for a door you left open.

Related reading on this blog: CHECK CONSTRAINT to Allow Only Digits in Column and What is Trusted Constraint in SQL Server? Interview Question of the Week #210.

Users are not the enemy. Missing rules are.

Bad data is not a user problem, it’s a table that forgot to say no.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.