Press "Enter" to skip to content

Category: Constraints

The Importance of Naming Constraints in SQL Server

Published 2023-03-15 by Kevin Feasel

Eitan Blumin gives everything a name:

This article was published by Aaron Bertrand a few years ago, talking about system-named constraints in SQL Server.

The article mostly focuses on the issue of naming conventions as the main issue with system-named constraints and provides a useful stored procedure script to generate sp_rename commands for all system-named constraints.

However, the script in the article provides the solution for only one database and doesn’t support the new “Edge Constraints” that were introduced in SQL Server 2019.

Check out Aaron’s article and Eitan’s follow-up piece.

Comments closed

Database Constraints in Postgres

Published 2023-02-03 by Kevin Feasel

Grant Fritchey does some data modeling:

PostgreSQL supports constraints much like any other database management system. When you need to ensure certain behaviors of the data, you can put these constraints to work. I’ve already used several of these in creating my sample database (available articles publicly on GitHub, in the CreateDatabase.sql file). I’ll explain those as we go. The constraints supported by PostgreSQL are:

Read on for the list, which includes one constraint which doesn’t have a direct analog in SQL Server.

Comments closed

Defending (Certain) Bad Practices

Published 2023-01-13 by Kevin Feasel

Aaron Bertrand considers the trade-offs:

For the first T-SQL Tuesday in 2023, Raul Gonzalez invites us to talk about cases where we have knowingly implemented worst practices.

Well, I have done it a lot. Most of the posts in my bad habits series are cautionary tales based on my own “learning the hard way.” There are always trade-offs with doing something correctly – maybe proper design is less efficient, or takes longer to write, or has to pass more checks. Over time, though, you start getting a feel for where it makes sense to cut these corners, and where it doesn’t.

Read on for some practical examples around Stack Overflow.

Comments closed

Deleting from Multiple Tables by Key

Published 2022-07-22 by Kevin Feasel

Guy 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 closed

Join Removal Due to Foreign Key Constraint

Published 2022-07-12 by Kevin Feasel

David 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 closed

Determining Why Constraints Are Untrusted

Published 2022-06-24 by Kevin Feasel

Tom 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 is CHECK_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 closed

Foreign Keys and Delete Operations

Published 2021-10-08 by Kevin Feasel

Kenneth 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 closed

Foreign Key Constraints and Blocking

Published 2021-06-18 by Kevin Feasel

Paul 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 closed