The challenge this week concerns constraints. In an SQL database constraints are used to enforce referential integrity on the data held within it. For example you can use foreign keys to ensure that a sales order in a database always has a reference to a valid customer ID in a customers table or use a table constraint to enforce that a purchase order number must be entered (i.e.: does not have a NULL value).
One thing you can do with a SQL database which is a bit naughty is disable the checking of the data on a constraint. Useful if you need to rework some underlying data in a pinch. Hold that. It’s not. What that does is store the problem up for later and probably for someone else to work out.
Constraints and foreign keys are there for a good reason which is to make sure your data is…you know…referential.
We have a software product that – when moved up to the cloud – now requires that referential constraints are enforced. As part of the upgrade procedure with the “designer” edition of the software (the environment that the data has to be upgraded to before getting shipped off to the cloud in this case) the consultant needs to enable an option in the software which effectively builds a SQL statement to do it across the database.
Sadly when that was enabled all hell broke loose. Well. Many foreign key violations at least. the software itself did not report any issues enabling trusted constraints but another tool which checks the database for correct preparation did.
After some SQL Profiler tracing I found 5 instances where data had escaped the foreign key constraint that was written. This happens usually when someone disables the constraint using the NOCHECK option, changes the data as they see and then re-enable the constraint with the CHECK option.
When you find a constraint violation within a SQL database you can query the problematic data by using the following command.
DBCC CHECKCONSTRAINTS (<constraint_name>)To check every constraint on the database – either enabled or disabled – use the following but be aware that this may take some time.
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTSOnce I then identified and resolved all the data issues I was then able to take the easy route and have the software enable all the constraints within the database and pass the upgrade check.
