Adam Machanic tweeted this advice last week:
Please do this. Thanks.
ALTER TABLE [your_table] ADD CONSTRAINT [start date must be less than end date] CHECK (start_date < end_date);
— Adam Machanic (@AdamMachanic) November 17, 2016
Are you missing any of these check constraints? Run this query to check.
This query looks for any columns in the same table that begin with “Start” and “End”. It then looks for check constraints that reference both these columns. If it doesn’t find them, it suggests a check constraint.
WITH StartColumnNames AS ( SELECT object_id, column_id, name AS column_name FROM sys.columns WHERE name like 'start%' ), EndColumnNames AS ( SELECT object_id, column_id, name AS column_name FROM sys.columns WHERE name like 'end%' ) SELECT t.object_id, OBJECT_SCHEMA_NAME(t.object_id) AS [schema_name], t.[name] AS table_name, s.column_name AS start_column, e.column_name AS end_column, N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + N'.' + QUOTENAME(t.name) + N' ADD CONSTRAINT ' + QUOTENAME(N'CK_' + t.name + N'_' + s.column_name + N'_' + e.column_name) + N' CHECK (' + QUOTENAME(s.column_name) + N' <= ' + QUOTENAME(e.column_name) + N');' as check_suggestion FROM StartColumnNames s JOIN EndColumnNames e ON s.object_id = e.object_id AND s.column_id <> e.column_id AND REPLACE(s.column_name, 'start', 'end') = e.column_name JOIN sys.tables t ON t.object_id = s.object_id WHERE NOT EXISTS ( SELECT * FROM sys.check_constraints c JOIN sys.sql_expression_dependencies start_dependency ON start_dependency.referencing_id = c.object_id AND start_dependency.referenced_id = t.object_id AND start_dependency.referenced_minor_id = s.column_id JOIN sys.sql_expression_dependencies end_dependency ON end_dependency.referencing_id = c.object_id AND end_dependency.referenced_id = t.object_id AND end_dependency.referenced_minor_id = e.column_id WHERE c.parent_object_id = t.object_id )
Don’t blindly run scripts that you got from some random guy’s blog. Even if that someone is me. That’s terribly irresponsible.
But this query may be useful if you do want to look for a very specific, simple kind of constraint that may match your business specs. These constraints are just suggestions and may not match your business rules. For example, when I run this query on Adventureworks, I get one “missing” check constraint for
HumanResources.Shift(StartTime, EndTime) and when I look at the contents of the
Shift table, I get this data:
Notice that I can’t create a constraint on this table because of the night shift. The constraint doesn’t make sense here.
Creating constraints on existing tables may take time if the table is huge. Locks may be held on that table for an uncomfortably long time.
Of course if your table has data that would violate the constraint, you can’t create it. But now you have to make some other choices. You can correct or delete the offending data or you can add the constraint with