Foreign key constraints exist to stop bad data before it happens, yet plenty of production SQL Server databases run without them. Is that a reasonable trade-off or a ticking time bomb? Greg Low looks at the most common reasons teams skip foreign keys, tests them against real-world experience, and explains why the answer differs for transactional systems versus analytic data warehouses.

Foreign keys are used to ensure referential integrity in relational databases. We don’t want to have orders for customers that don’t exist, or have lines on the orders that refer to products that don’t exist. That seems straightforward enough – and a good idea – so why is there an endless discussion in the development community about whether databases should include declared foreign key constraints or not?

Before we get started, it’s worth noting that the answers can be different depending on whether we’re talking about a transactional system or an analytic system; they are not the same. So, I’ll discuss them separately, starting with transactional systems.

Why do some people/teams avoid foreign key constraints?

As a mentor or consultant, I visit a wide variety of client sites – many of which have applications that were designed without constraints. When I ask the reason for this, the response is invariably one of the following:

Most of the sites I work with have sizeable databases, making it even more important to talk about why they’re designing their applications without constraints. Let’s take a closer look at the reasons given.

“We don’t need them because the app ensures that it’s right”

In enterprises, most large databases end up being used by more than one single application, with each of these often made up of many sub-applications or modules. This means you’re depending upon every place that accesses the data applying the exact same rules. (This issue applies to other forms of constraints as well).

Worse still, the databases often end up being accessed by applications from different authors, often with different technology stacks. ETL (extract, transform, load) processes are often used to move data into the databases, or to update the data. And, in real production scenarios, data fixes are often applied directly to the database!

Having the view that “it will be OK because everything goes through a single application, and it never has bugs that affect referential integrity”, is a very narrow view of the world.

When I see this in production systems, I often check the references in the data. What I find is consistent: on any large system that has run for quite a while, I usually find issues.

When presented with these issues, the data team members first look puzzled…before remembering some issue that happened a few weeks ago. “Ah yes, we had that bug last month and thought it was fixed”. And that issue is often with an ETL process – not the application itself!

“They don’t work well with our application development”

In most cases, this boils down to the fact that developers don’t want to work out the order they need to use for updates. Without constraints, they can update any table they want, in any order, which is much easier for them.

The downside to this: the data is periodically in an invalid state. While it will (hopefully) eventually become consistent, this also means that the data has many interim states that are actually invalid states. What happens with concurrent access at that point? Or, a reporting application that finds invoices for customers that don’t actually exist?

It would be helpful if SQL Server supported deferred constraint checking but, as of the time of writing (August 2026), it doesn’t. Having deferred constraint checking would allow you to start a transaction, do whatever you want in whichever order you want, and then commit.

The guarantee you’re providing is that, no matter what you do during the transaction, it’ll all make sense at the end.

I’ve been requesting this to the SQL Server team for decades, to no avail. I still think it’s one of the most important enhancements that could be made to the platform from a development point of view. Developers would love it.

“They are too slow”

I’m often told “we can’t do it because it would be too slow”. I then ask “have you tested it?” and, invariably, they admit that they haven’t. The reality is usually that someone’s brother’s cousin’s friend read it somewhere on the Internet, so they decided it would be a problem!

Whenever I test the performance impacts of foreign key constraints, I find very little impact as long as appropriate indexing is in place. It also helps if sensible options are chosen when bulk importing data. I’m not saying I never see performance issues that a foreign key has added to – but these situations are few are far between.

If I do encounter one of these situations, I disable rather than delete them. I like to see the disabled constraint still be in place so it can be discovered by tools (but not checked). It certainly never applies to all constraints in a database.

“What’s a foreign key constraint?”

Sadly, this is also a common question – but thankfully one I mostly only hear from small teams with small databases/ applications. Some developers just don’t understand the issue – but not most developers.

The bottom line for transactional databases

When I carry out detailed checks on a system that has run without constraints for quite a while, I nearly always find data integrity issues.

The customers always say they’re surprised, but quickly realize they shouldn’t have been. These issues are OK if you’re building a toy application, but not OK if you’re building a large financial one.

At the very least, sites that decide not to have constraints should have a process in place that periodically checks data integrity. Most don’t even do this, but it’s important. Finding issues as soon as they occur is better than finding them later down the line. The longer an issue is left, the harder it is to fix.

With constraints, you at least get instant feedback that something’s wrong. If you have a bug in your ETL process that’s messing up your data in some subtle way, for example, you don’t want to find it several weeks later, after yet more processing has occurred on that data.

Do analytic data warehouses need foreign keys too?

If you’ve worked seriously with analytic data warehouses in recent years, you’ve probably noticed that most either skip foreign key constraints entirely, or let you define them without ever enforcing them. I understand this line of thinking but am torn on the situation.

For example, they’ll often say that the source system is responsible, ignoring the fact that many data warehouses have multiple source systems. Remember, part of the reason the data warehouse exists is to centralize the data for reporting. So, in this case, there’s no one system that’s checking the referential integrity.

This also ignores the very real possibility that the ETL processes could have bugs and mess up your data integrity. Given how long many of these processes run, if something’s wrong I’d rather find out as early as possible – not right at the end when there’s little to no time to reload the data.

At least with the “define but don’t enforce” option, the tools can be used to work out how data is related. What I’d rather see here is a way to define logical relationships that the tools can discover. Most of the time, we’re loading semantic models from views layered over the data warehouse tables, so we don’t have any concept of foreign key relationships anyway.

So, another key enhancement I’d like to see SQL Server and other database engines have, is a standardized way to describe logical relationships. I’d use that all the time for views.

Summary: disable, don’t delete, foreign key constraints

For transactional systems, I like to see foreign key constraints in place until it’s proven that a particular constraint can’t be in place for a performance reason. Then, I like to see it disabled rather than removed. A bonus is if it can still be discovered by tools (and can easily be used during testing, even if later disabled in production.)

Finally, the two things I’d like to see the SQL Server team work on are deferred constraint checking and discoverable logical relationships.

What do you think? Do you agree or disagree with any of my points? I’d love to hear your thoughts in the comments below.