A Fine Slice Of SQL Server
Tom Collins just wants the entire list:
Question: How can I list all Foreign Keys referencing tables and columns within SQL Server tables?
Click through for the script. In addition, it’s easy to modify this script to filter down based on a given table or referenced table, which will probably be more common scenarios than getting all of the key constraints at once.
Comments closedGuy Glantser needs to delete some data:
Sometimes you need to delete data from multiple tables in a database. For example, you might have a multi-tenant database, and you need to delete all the data that belong to some tenant.
The problem is that there are many tables in the database that contain data, which you need to delete. If you have a column like “TenantId” in all tables, then your life is easier, because you have a simple predicate to apply to your DELETE statements against all tables. But even then, if there are foreign keys between tables to enforce referential integrity, then things get more complicated.
Read through for Guy’s answer, which definitely works and can be the quickest solution. If you can’t drop foreign key constraints (even temporarily), I have a post from a while back on tracing foreign keys to “levels.” The post only covers finding the ordering but could be extended to delete data one level at a time.
Comments closedDavid Alcock shows a performance benefit from having a foreign key constraint in place:
Foreign keys are used in database design to enforce referential integrity but they also have some performance benefits as well that you might not necessarily notice unless you’re looking into your execution plans.
Let’s take the following query using the AdventureWorks2019 sample database where I’m selecting the BusinessEntityID and JobTitle from the HumanResources.Employee table and by using an inner join I’m only returning rows that have matching values (BusinessEntityID) in both tables:
There are specific rules to table elimination but if you meet the criteria, it can save you a bit of CPU time and I/O.
Comments closedTom Zika adopts a zero-trust constraint architecture:
Okay, so you went through the effort of fixing them, but the next day your constraints are not trusted again. What gives?
If you are sure none of the DBAs or developers is doing this to spite you, the most common culprit is a BULK INSERT or bulk copy tool (bcp).
One of its parameters is
-h(hints)
and one of those hints isCHECK_CONSTRAINTS
Read on to see how this can mess everything up, as well as how you can track and fix it. There are cases, particularly in extremely high-write systems, where you don’t necessarily need the constraint to exist but want it to be there for documentation purposes. In that case, the constraints are usually disabled rather than simply untrusted. The other time I see people purposefully using untrusted constraints is that old data is garbage and essentially unfixable but they want new data to be correct. Most of the time, though, constraints are untrusted because nobody noticed the problem.
Comments closedGreg Dodd gives it away in the title:
I needed to add a Unique Constraint today to a table. We could go ahead and just add a constraint, but the data I wanted to constrain on was already indexed. Could I just make that a Unique Index and be done with it? Let’s find out with the following table:
Let’s, shall we?
Comments closedKenneth Fisher takes us through a case of deleting rows:
Deleting rows from a table is a pretty simple task right? Not always. Foreign keys, while providing a ton of benefits, do make deletes a bit more complicated.
Click through for an example of this, as well as a quick discussion of cascading deletes, which sound really useful until you make a big mistake. The other problem with cascading deletes is, even if you do intend to delete everything noted, the process is a lot slower than what you can do in batches, and you’re liable to increase the size of your transaction log file to boot.
Comments closedPaul White takes a look at blocking due to foreign key checks:
This article covers one such consideration that does not receive much publicity: To minimize blocking, you should think carefully about the indexes used to enforce uniqueness on the parent side of those foreign key relationships.
This applies whether you are using locking read committed or the versioning-based read committed snapshot isolation (RCSI). Both can experience blocking when foreign key relationships are checked by the SQL Server engine.
Under snapshot isolation (SI), there is an extra caveat. The same essential issue can lead to unexpected (and arguably illogical) transaction failures due to apparent update conflicts.
This article is in two parts. The first part looks at foreign key blocking under locking read committed and read committed snapshot isolation. The second part covers related update conflicts under snapshot isolation.
Definitely worth reading the whole thing.
Comments closedChris Johnson has a how-to guide:
That all looks ok, and everything published fine, and kept on publishing fine until I happened to check the generated script for some of the publishes (I was in the early stages of development at this point so deployments were just being done through Visual Studio direct to my machine).
When I did I noticed that every deployment I was getting code like this:
That’s dropping all my constraints, recreating them using
WITH NOCHECK, and then usingWITH CHECKto check the existing data. Obviously all the data will pass those checks, but that’s going to add some time to my deployments, and as the size of the data and the number of checks both increase, these checks will take more and more time.
Read on to understand what’s happening. I’d call this a fairly silly limitation on the part of SSDT.
Comments closedKevin Wilkie tempts Betteridge’s Law of Headlines:
Foreign Keys are truly columns that point to the primary key of another table. Yes, you can create a foreign key pointing to the same table – but that’s… well… special. There are reasons, but far beyond the reasons I want to go into foreign keys here.
What a foreign key does for you is extremely important – Enforce Data Consistency.
Click through to learn how.
Comments closedLouis Davidson has an explanation for us:
Edge constraints were added in SQL Server 2019 to make the node to edge relationship stricter/enforced, and more like typical foreign key constraints. When used, they define what node types can be used in the from and to position of the edge. What makes edges different than a many-to-many relationship in a relational table is that an edge can implement more than one many-to-many relationship in a single table. To constrain the types of data that can be put into the edge, you can use an edge constraint.
Edge constraints are very similar to implementing foreign key constraints, but there are a few key differences. Foreign keys are between two tables. Edges are between one edge table, and multiple pairs of node tables. In both cases, you can have multiple constraints, even from the same table to the same related table on the same column. However, with edge constraints, because you can have multiple pairs of expressions, and even multiple constraints, it bears discussion. If you have more than one constraint, it has one big negative, but it is allowed to implement one big positive!
Click through for the explanation, as well as an example.
Comments closed