Basic DBA guidance for System Administrators

I knocked this up just this morning for our System Administrators, because I was sick of providing specific explanations of each and every constraint violation they receive when performing regular updates to staging & client environments.

1. Identify the Table referenced in the error message. 
2. Identify the Foreign Key in the error message. 
3. (assuming SSMS) Connect to the database in question.
4. Drill down to the definition of the TABLE you identified from the error message above. Expand the ‘Keys’ node & look for the constraint mentioned in the Error message. If you do not see it (such as in this case), it means that the constraint is defined on the OTHER SIDE of the association. We stick to standard naming conventions here, so constraints should mostly be named ‘FK_{FirstTable}_{SecondTable}’. From this we can deduce that the table on the other side of this association.
5. Script out this object in the database to see exactly what data integrity it is enforcing.
6. 1.       In this, you are interested in the tables & column names. This part:
   FOREIGN KEY([IdentifierColumnName])
   REFERENCES [dbo].[Table2] ([ IdentifierColumnName ])
7. This clearly tells you that this constraint ensures that records in {FirstTable} which define a value for {IdentifierColumn} must define an ID which exists in {SecondTable}.
8. To identify the records violating this constraint (which will need to be deleted before the constraint can be re-enabled), construct a query such as:

select * from {Table1} where {IdentifierColumn} not in (select {IdentifierColumn] from {Table2})

if this doesn’t reveal anything, reverse {Table1} and {Table2}.