The Strategy: Never trust your data source blindly. While frontend validation is great for UX, Database Constraints are your last line of defense. By adding a CHECK CONSTRAINT, you ensure that no invalid email address ever touches your storage, whether it comes from an API, a bulk import, or a manual script.
The Goal: To ensure the Email column always contains at least an @ symbol and follows a basic structure.
Option 1: The Simple “@” Check
If you only want to ensure the presence of the @ symbol (and allow NULLs if the column isn’t mandatory), this is the fastest and most efficient way:
-- Ensure '@' exists or the value is NULL
ALTER TABLE Users
ADD CONSTRAINT chk_email_basic
CHECK (CHARINDEX('@', Email) > 0 OR Email IS NULL);
Option 2: The “Pattern Match” (More Robust)
Want to go a step further? You can use the LIKE operator with wildcards to ensure the email looks like a real address (e.g., something before the @, something after it, and a dot):
-- Ensures format like: x@x.x ALTER TABLE Users ADD CONSTRAINT chk_email_pattern CHECK (Email LIKE '%_@__%.__%');
🔍 How the Pattern '%_@__%.__%' Works:
%_: At least one character before the@.@__: The@symbol followed by at least two characters.%.__%: A dot followed by at least two more characters (the domain extension).
⚠️ What happens if a constraint is violated?
If a user or a script tries to INSERT or UPDATE a row with an invalid email, SQL Server will immediately block the transaction and throw an error:
"The INSERT statement conflicted with the CHECK constraint 'chk_email'..."
Summary
Constraints are “set and forget” security for your data. Using CHARINDEX is lightweight, while LIKE patterns offer better structural enforcement. Choose the one that fits your business rules and keep your database clean!