Scripting Foreign Key Constraints

Louis Davidson has a process to script out foreign key constraints and includes an example which loads those constraints into a utility table:

As noted in my previous post, I am in the middle of building a utility (for work, and for my next SQLBLOG post), that will help when you need to drop the foreign key constraints on a table, but then replace them without having to save off the script manually. In that post, I showed how to manually create a foreign key constraint in three possible states.

Next, in order to create the utility to script the FOREIGN KEY constraints before dropping them, I need a way to script the constraint. In this post I present a user defined function that will generate a script that does the scripting of a foreign key constraint. The code is largely based on some code from Aaron Bertrand in this blog (with his permission naturally!) with a few edits to script constraints as enabled, disabled or untrusted if the source constraint was in that condition (or you can force the constraints to a certain way if you so desire as well.)

This could be particularly useful for ETL jobs.

Foreign Key Check Options

Louis Davidson shows how to create a foreign key constraint which is enabled or disabled, trusted or untrusted:

I am in the middle of building a utility (for work, and for my next SQLBLOG post), that will help when you need to drop the foreign key constraints on a table if you want to truncate the tables, but holds the script in a table to replace the script.  The first thing though, is to make sure I have all of the scripting possibilities understood.

When I started hunting around to remember how to create a disabled constraint, I couldn’t easily find anything, so I figures I would make this a two-parter. (My blogging rule is if I look for something and find a good article about it, reference it, then tweet the article out. If it is too hard to find, blog about it!) So today I will review how to create a FOREIGN KEY constraint in three ways:

  • Enabled, and Trusted – Just as you would normally create one

  • Enabled, Not Trusted – The “quick” way, not checking data to see if any wrong data already exists, but not allowing new, bad data in

  • Disabled, Not Trusted – The constraint is basically documentation of the relationship, but you are on your own to make sure the data matches the constraint

In an ideal world, all of your constraints are enabled and trusted, but when you’re building a general-purpose script, you can’t always assume that will be the case.  Click through for examples on how to create foreign key constraints fitting each of these scenarios.

Default Column Storage

Paul Randal explains how default column values are stored:

And selecting the initial 10 rows can be demonstrated to return the 3rd column using the initial default set in step 3. (It makes no difference if any rows are added between steps 3 and 4.)

This means that there *must* be two default values stored when a new column is added: one for the set of already-existing rows that don’t have the new column and one for any new rows. Initially these two default values will be the same, but the one for new rows can change (e.g. in steps 4 and 5 above) with breaking the old rows. This works because after the new column is added (step 3 above), it’s impossible to add any more rows that *don’t* have the new column.

And this is exactly how it works. Let’s investigate!

In typical Paul Randal fashion, this is both a look at internals and an interesting explanation.

Guessing At Foreign Key Relationships

Daniel Hutmacher has put together a script to try to find hidden foreign key relationships in a database:

Now, before you go crazy with this stuff, remember, it’s not a magic bullet, but rather some automation help to save you some coding and to help you review your data model. The script doesn’t change the database, it only prints out its suggestions, and this is totally by design.

  • For this to work, you’ll obviously need proper primary keys or unique indexes on your referenced tables.

  • We’re working on the assumption that the referencing and referenced column names are the same. Go ahead and change the script to suit your naming standards (look for the comment in the CTE)

  • The script has no domain knowledge of your database, some of the suggestions are probably going to be downright silly.

This is a good first pass approach, especially if you have a larger database completely lacking in relational integrity.

Indexing Foreign Keys

Kim Tripp looks at the practice of adding non-clustered indexes on columns which make up foreign key constraints:

I’m going to tie today’s post with a question I received recently: if I have a table that has 6 foreign key columns/references should I create one index with all 6 foreign key columns in it, or should I create 6 individual indexes – one for each foreign key reference.

This is an interesting question with a few tangents to cover for our SQL101 series. I consider indexing foreign keys as part of my “Phase 1 of index tuning.” But, let’s go back to some basics before we dive into the answer on this one.

Read on for Kim’s thoughts on the topic and some good practices.

Non-Trusted Foreign Keys

Daniel Janik explains what happens when you don’t have trusted foreign key constraints:

Why is it untrusted? Perhaps we disabled the check to load data and neglected to re-enable it?

No matter what the reason is the next part is not as simple. This is for two reasons.

  1. The data in the child table may not be valid. Since the key was not being checked I may have data in my table that isn’t represented in the parent.

  2. The syntax is a bit silly. As Mike Byrd in Austin, TX says, Microsoft studders. The syntax to reenable is “CHECK CHECK”. Let’s look at how we reenable the Address key check.

Read on for pros and cons of disabling (or not trusting) foreign key constraints.

Uniqueness And Multiple NULL Values

Dennes Torres shows how to allow an indefinite number of NULL values  while guaranteeing non-NULL values are unique:

Regardless of using unique constraint or unique index, the field can accept null values, however the uniqueness will result in only accepting a single row with null value.

The solution to allow nulls in unique fields is create a unique filtered index excluding the nulls of the index, due to that the uniqueness of the nulls will not be validated and multiple rows with nulls will be accepted.

Click through for the code.  I enjoy asking this as an interview question.  It’s a non-trivial problem with a non-trivial solution and isn’t a trick question.

Check Constraints On Date Ranges

Michael Swart has a script to check start versus end dates to ensure there is a valid check constraint for date ranges:

Don’t blindly run scripts that you got from some random guy’s blog. Even if that someone is me. That’s terribly irresponsible.

But this query may be useful if you do want to look for a very specific, simple kind of constraint that may match your business specs. These constraints are just suggestions and may not match your business rules. For example, when I run this query on Adventureworks, I get one “missing” check constraint for HumanResources.Shift(StartTime, EndTime) and when I look at the contents of the Shift table, I get this data:

If you wish non-blindly to run a script you got from that guy’s blog, click through for the script and more details, including a discussion of false positives.

Indexing Foreign Keys

Kendra Little answers a question about indexing foreign key constraints:

We recently had a SQL Server performance assessment. It remarked on two things that made me think:

1 ) tables with foreign keys but missing supporting index
2 ) tables with indexes that are not used

But in our case the remark in Case 2 was on a index that supports a foreign key!

Curtain down!

Now to my question, in which cases should you as a rule create indexes to support a foreign key?

I think Kendra elaborates the pros and cons well.  I’d lean against automatically creating indexes because I’ve worked with scenarios in which you don’t drive from the Parent to the Child; instead, you drive from Child to Parent or Something to Child to Parent and those indexes go unused.

Constraints Or Unique Indexes

Kendra Little discusses unique constraints versus unique indexes:

Unique constraints and unique nonclustered indexes have a lot in common: unique constraints are implemented with a unique index behind the scenes.

While unique nonclustered indexes have a couple of additional features, I can still think of a scenario where it might be best to use unique constraints.

The value I see in unique constraints over unique indexes is semantic:  it says to everyone, “I’m supposed to be unique by virtue of my existence.”  They’re part of the data model, whereas indexes are performance optimizations to handle common data paths.


May 2017
« Apr