Foreign Keys and Referential Integrity

Foreign Keys and Referential Integrity

Today's post has nothing to do with the gym. I couldn't think of a good analogy and I encountered a problem with Foreign Keys this week. Thought it would perhaps make for an interesting read.

Let's start with some basics. If you're in the database space or even just beginning your journey, chances are you're familiar with Primary Keys. A primary key is a completely unique field within a table or combination of fields that make the data unique to the table. If you ever attempt to insert a record into a table with the same value, you receive the good ole' "VIOLATION" statement in red.

When you create a primary key, the database engine will automatically create a Primary Key index. So at the very least, you have one on your table for purposes of locating data quickly. Your primary key oftentimes but not always be clustered. Meaning that the data in your primary key field is in sequential order. It does not have to be though. Your primary key field could be GUIDs for example that are randomly generated and therefore could be all over the place. Your index will still sort them accordingly, but it will be nonclustered within the table; not in order.

What's a Foreign Key?

A foreign key within a database table is a field that references another field in another table.  This creates referential integrity. With this in place, data cannot exist in Table A that doesn't exist in Table B. Let me show you with a crude diagram.

Let's look at our Products Table in the middle. We've got our primary key of ProductID, then a couple of columns with some descriptors; a type and color.  Those are foreign keys to the ProductTypes and ProductColors tables.  

Say that our ProductTypes Table contains the following values:

• valve
• pipe
• clamp

With a foreign key in place on that field, if we attempt to add a product into the ProductsTable with a ProductType of "flange", we'll receive an error that it violates the foreign key relationship. A foreign key ensures that data integrity remains between the tables. Of course the same goes for the ProductColors table.

Let's go the other direction though with our Orders table. Consider we have a foreign key on the Product field in Orders that references the ProductID field in ProductsTable. That ProductID must exist in the root table in order for us to put a record in the Orders table. That makes perfect sense, we can't sell something that doesn't exist right?

Referential Integrity! That's great stuff.  It helps keep your database neat and tidy. Forces you to consider the ramifications of modifying data.  

You can find your foreign key information in the sys.foreign_keys view in SQL Server. In there is a field named "is_not_trusted". That value should be 0 indicating that the relationship is good and no values exist in one table and not the other. 

Until you want to delete....

This is the issue I encountered this week. The table structures I'm working with are more in depth than my example above, but it'll still work for the purposes of education. We have a purge process that deletes records older than a specified date. The root table had foreign keys pointing TO it from about a dozen other tables. Meaning the ID field in my root table was being used in those other tables as a reference.

A good way (and how the vendor was doing it) was to first delete the records in the lower tables so that the referential integrity would remain in tact. Consider our example structure above. If we decided to no longer offer Product X and wished to delete it from the ProductsTable, we'll encounter a problem.  Remember that the product has to exist because of the foreign Key to the Orders table. However, if we deleted all of the records in the Orders table with that product first and THEN deleted from the ProductsTable, success. By deleting every record in Orders where Product = X, there's no references to that anymore in the Orders table. So we can delete the product now.

This works fine when you're working with a small set of data. Still using our example above, perhaps our Orders table is small and when we do our delete operations it goes fairly quickly. However, consider if we've run the business for 5 years and our Orders table is massive. We decide to no longer offer product X and delete all orders where Product=X. Then we go to delete the actual product. And we wait....and wait.....and wait. It's only a single row, why is it taking so long? 

Even though we've purged the records from Orders where Product=X, because of the foreign key relationship, SQL is still going to read every record in the Orders table to make sure that no records exist. Just because you deleted them all already, SQL doesn't "know" that. It wants to verify that no records exist so it can maintain referential integrity.

That's precisely what I encountered in my example at work this week. Purging historical data ran great on all of the referencing tables. However when it came to the root table, it churned for hours doing multiple millions of logical reads. That's when I discovered all of the foreign keys referencing the root table. SQL was doing full table scans on those "lower" tables to make sure the data was good. Even though we'd already deleted the referenced records. And it does so for every record being deleted. So even if I'm only deleting 100 rows, that's 100 times it does a full table read on all of those "lower" tables.

How did I get around it?

It is possible to temporarily disable these checks allowing you to manipulate data in all referenced tables without failure. I urge caution when doing this as when you attempt to re-enable the checks, if you've removed or changed something that breaks the integrity, you will get an error and that could impact applications.

ALTER TABLE ProductsTable NOCHECK CONSTRAINT FK_Name

This statement will turn off the checking of data. The Foreign Key is still there, not deleted. It simply skips the check. You would also see that in the sys.foreign_keys table, the "is_not_trusted" field now shows a 1.

So in my case, I turned off the checking for all of the Foreign Keys to the central table and the delete operation went from 2+ hours to about 2 minutes! Upon completion of the job though, I immediately re-enable those checks.

ALTER TABLE ProductsTable WITH CHECK CHECK CONSTRAINT FK_Name

This statement will re-enable the foreign key and run a check to ensure the data integrity is in place. This will take some time. However, in my case, it did full reads on each "lower" table once and not multiple times.

Conclusion

Foreign keys are a great way to ensure data is neat and tidy throughout your tables. They make great join points when running queries (if indexed properly). Just like anything when it comes to the realm of databases, make sure that you architect well, consider scenarios such as historical purging and how you'll get around it.

I hope you learned something today! Make the day great!