Press "Enter" to skip to content

Category: Constraints

Fun with Multi-Column Unique Constraints

Published 2021-02-22 by Kevin Feasel

Aaron Bertrand has an interesting use case:

A problem that comes up occasionally with constraints in SQL Server is when a unique constraint applies to multiple columns, but the values in those columns can be populated in any order. For example, if a table holds interactions between two users, and has columns User1 and User2, there should only be one row allowed for users 100 and 200, regardless of whether the data is entered as 100, 200 or 200, 100.

Click through for one solution. Another solution would be to normalize this down further with a dbo.ConversationParticipants table.

Comments closed

Foreign Keys and Updating the Parent

Published 2020-12-21 by Kevin Feasel

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

Published 2020-12-08 by Kevin Feasel

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

Published 2020-11-27 by Kevin Feasel

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

Published 2020-03-13 by Kevin Feasel

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

Published 2020-02-04 by Kevin Feasel

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

Published 2020-01-20 by Kevin Feasel

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

Published 2019-06-03 by Kevin Feasel

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

Published 2019-03-13 by Kevin Feasel

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