FK value not exsits in the Primary table it referenced to

Question

FK value not exsits in the Primary table it referenced to

sqlfriend

SSC Guru

Points: 52794
More actions
September 5, 2015 at 2:31 pm

#302891

I am migrating data. I found a strange thing in the existing table, there is a column named workshopCaseID in a TruancyCase table ,The datatype for workshopCaseID is an int (null). it is a FK, there are some records are 0 values, but the fk referenced primary table only have 1-8 values, how are the 0 values get inserted there?
Thanks!

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 1

Probably the foreign key was dropped at some point, the data was put in, and then the foreign key was reapplied using the WITH NOCHECK option. That will make it so that it doesn't validate the data in the foreign key. It's a dangerous practice as you just found out.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

WayneS SSC Guru Points: 95461 More actions · Answer 2

The proper way to enable a constraint so that it ensures that all data is consistent is:

ALTER TABLE <tablename> WITH CHECK CHECK CONSTRAINT <constraintname>;

If there is any data that doesn't pass the constraint, you'll get an error.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

sqlfriend SSC Guru Points: 52794 More actions · Answer 3

Second thought, I can see records that entered 2 months ago, pretty recent, and we did not change any schema, or drop constraints , disable contraints, etc. so still a little suspect how this happened?

WayneS SSC Guru Points: 95461 More actions · Answer 4

sqlfriends (9/7/2015)
Second thought, I can see records that entered 2 months ago, pretty recent, and we did not change any schema, or drop constraints , disable contraints, etc. so still a little suspect how this happened?

Disabled constraints doesn't prevent data entry that violates the constraint.

Subsequently enabling the constraint only prevents new data modifications that violates the constraint - any existing data is not validated.

To see if you have any constraints that aren't trusted (it was disabled, and subsequently enabled, but existing data hasn't been validated), run this query in that database:

SELECT name AS FK_Name, OBJECT_SCHEMA_NAME(parent_object_id) AS TableSchema, OBJECT_NAME(parent_object_id) AS TableName, is_not_trusted

FROM sys.foreign_keys

WHERE is_not_trusted = 1;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

RonKyle SSC-Dedicated Points: 31574 More actions · Answer 5

Second thought, I can see records that entered 2 months ago, pretty recent, and we did not change any schema, or drop constraints , disable contraints [sic], etc. so still a little suspect how this happened?

Something must have happened, even if the evidence is not available to reconstruct what it was. Have you tried entering a record with a 0 for that field to see what happens?

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 6

sqlfriends (9/7/2015)
Second thought, I can see records that entered 2 months ago, pretty recent, and we did not change any schema, or drop constraints , disable contraints, etc. so still a little suspect how this happened?

There's no magic situation where a foreign key exists, created using WITH CHECK, is enabled, no changes to this situation, and you still get bad data. It doesn't happen. Something dropped and recreated the foreign key or disabled it and re-enabled it.. Use Wayne's query above to check it out.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning