Press "Enter" to skip to content

Category: Constraints

Foreign Keys and Updating the Parent

Hugo Kornelis conclues a mini-series on foreign key constraints:

Welcome to part fourteen of the plansplaining series, where I wrap up the mini-series on how simple foreign keys have huge effects on execution plans for data modifications.

We already looked at inserting data in the referencing (child) table, and at deleting data from the referenced (parent) table as well as updates in the child table. We did not and will not look at deleting from the child table or inserting in the parent table: those operations can by default never violate the foreign key constraint, so no additional logic is needed.

So that means there is only one thing left to explore: updating the parent. Perhaps surprisingly, this is actually quite complex, so it warrants an entire post of its own.

Read on to see why.

Comments closed

Execution Plans: Foreign Keys and Deletion

Hugo Kornelis continues a series on execution plans:

In the previous part, we looked at foreign key checking when data is inserted. Inserts in the referencing (child) table can, by definition, not cause foreign key violations, so no checking is done in those cases. But for inserts in the referenced (parent) table, SQL Server needs to check that the inserted value does indeed exist. We saw how both the Nested Loops and the Merge Join operators have some specific optimizations to do that check as efficient as possible; we also saw how the Assert operator does the actual checking.

When deleting data, the reverse is true. We can delete rows from the child table without verification, but deletes from the parent table are subject to a check.

And those deletes can get crazy expensive.

Comments closed

How Foreign Keys Appear in Execution Plans

Hugo Kornelis shows us how foreign key contraints modify certain execution plans:

The top left of this execution plan looks very unsurprising. A Clustered Index Scan to read all rows from the #Products temporary table, and those rows are then passed to a Clustered Index Insert operator for insertion into our permanent table dbo.Products. But wait? Why is there no table or index name listed below the operator name? That’s actually because the operator has not one but three items in its Object property. When a Clustered Index Insert targets just a single index, SSMS can work out the name and show it. But when Clustered Index Insert targets multiple objects, SSMS plays it safe and displays none.

Read on to learn more.

Comments closed

Supporting Multiple NULL Values with a Unique Constraint

Itzik Ben-Gan walks us through a workaround in T-SQL:

Suppose that you have a database in a platform that supports the standard unique constraint and you need to migrate that database to SQL Server. You may face issues with the enforcement of unique constraints in SQL Server if the unique columns support NULLs. Data that was considered valid in the source system may be considered invalid in SQL Server. In the following sections I’ll explore a number of possible workarounds in SQL Server.

I use a simplified version of this as an interview question, so it’s nice to see an entire article from Itzik on the topic, including a couple solutions way outside the box.

Comments closed

Finding Foreign Key Relationships in SQL Server

John Morehouse shows how you can piece together foreign key relationships in SQL Server:

Recently, I had to purge some parent records from a table.  In this case, the parent table had foreign keys, which itself isn’t an issue.  The fact that there were more than 30 of them was.   While SQL Server will happily tell you that you are violating a foreign key if a child record is present when deleting the parent record, finding all of them can be cumbersome.  This is even more true when you have a larger number of foreign keys.

Thankfully, SQL Server can tell us a lot of information about foreign keys including both the parent and child tables as well as the column used.  From this information, we can dynamically create a SELECT statement that would tell us the number of child records that are tied to the parent ID.

Click through for the solution.

Comments closed

Finding Data Which Breaks Constraints

Phil Factor has a procedure to test disabled check constraints and find data which would cause an error:

However often I go on about CHECK constraints, there will always be a developer who will leave them out or mutter in a dignified manner about how all checks need to be done only at the application level. This attitude soon gets divine retribution. Bad data springs up like a rotting fungus over your database unless you add CHECK constraints to all your tables. This is fine but then how do you prevent the excellent and estimable habit of adding them to then interfere with a release? The constraints will stop the build if they meet bad data: it is what they are trained to do. If you don’t like that, then you must fix the bad data first.

Click through for the process.

Comments closed

The Joy of Non-Nullable Persisted Computed Columns

Louis Davidson shows what you can do with persisted, non-nullable computed columns:

Next, let’s add a check constraint our computed column. For this example, we are just going to make sure that the value in the table is a palindrome (because this is something that every data architect has come across at least one in their life, right?). So Value = REVERSE(Value);

Read on for more fun and sometimes-useful things you can do.

Comments closed

Changing Constraints in Near-Zero Downtime Situations

I have part six of my interminable series on near-zero downtime deployments:

The locking story is not the same as with the primary and unique key constraints. First, there’s one extra piece: the transition will block access to dbo.LookupTable as well as the table we create the constraint on. That’s to keep us from deleting rows in our lookup table before the key is in place.

Second, the locks begin as soon as we hit F5. Even SELECT statements get blocked requesting a LCK_M_SCH_S lock. Bad news, people.

So what can we do to get around this problem? Two routes: the ineffectual way and the ugly way.

Despite my being a ray of sunshine here, you should still check this out. It’s shorter than the average Russian novel, at least.

Comments closed

Implicit Parent Reference On Foreign Keys

Deborah Melkin shows us an interesting way of creating foreign keys:

No matter how long you work with something, you can always find something that you never knew before. I found one about foreign keys this week.

I was reviewing SQL scripts for coworkers and I noticed that the foreign keys were written without referencing the parent table’s column. But the script didn’t fail and created the foreign keys correctly. So how did this work?

I don’t think I’ve ever seen this syntax either. I’m not a big fan of it for the same reason that Deborah isn’t a big fan of it: adding a couple more words does clarify your intent, and so add the words.

Comments closed