24May 2021 by Grant Fritchey 2 Comments

Database Fundamentals #30: Create Foreign Keys With T-SQL

Database Fundamentals
You can create foreign keys using TSQL roughly the same way as you created primary keys. You can either use the ALTER TABLE statement to add the foreign key, or, if you already have the parent table created along with it’s primary key, you can use the CREATE TABLE statement to include foreign key constraints. The restrictions for creating foreign keys are still the same when using TSQL. Adding a Foreign Key Using the ALTER TABLE statement is very straight forward as before. This script will create a foreign key relationship between the Personnel.Person table and the Personnel.PersonAddress table: ALTER TABLE Personnel.PersonAddress ADD CONSTRAINT PersonAddress_FK_Person FOREIGN KEY (PersonID) REFERENCES Personnel.Person (PersonID); The ALTER TABLE and ADD CONSTRAINT statements are the same as what you saw before. They respectively refer to…
Read More
09Sep 2015 by Grant Fritchey 16 Comments

Yes, Foreign Keys Help Performance

SQL Server
I created this example several years ago that illustrates how foreign key constraints can help performance. It's a contrived example. Granted. I feel like it illustrates the point. However, over the years, people have questioned one aspect of it. The optimizer uses the foreign keys to figure out which tables can be eliminated from the query, making for a more efficient plan and making the query run faster. The pushback has always been, "Yeah, Grant, but nobody writes T-SQL where they include extra tables that they don't need." My initial response, after I stop laughing, is to point out any number of ORM tools. But, you know what, let's assume that's correct. No one would ever create a giant catch-all view that has all their JOINs in one place so they don't have…
Read More
13Sep 2013 by Grant Fritchey 2 Comments

Are Foreign Keys Better Than Indexes?

SQL Server, T-SQL
When I first saw this question I thought to myself, "Self. Don't you think that's comparing apples to hammers? Yes, Self, I'm pretty sure it is. Good, I thought so too, self. Yeah, me too." After rebooting because of the runaway iterations on that thought, I had another, "Well... hold on there self. Both types of objects, while pretty different, are taken into account by the query optimizer." I then had to admit to myself that I had a point. So the question remains, are foreign keys better than indexes? As my first self said, these are different objects and they fulfill different purposes within SQL Server. My second self wants to point out that when you're dealing with functional objects within SQL Server, it's a bad habit to start…
Read More
22Nov 2010 by Grant Fritchey 48 Comments

Do Foreign Key Constraints Help Performance?

SQL Server, T-SQL
Most people are very aware of the fact that having a foreign key constraint in place on your tables adds overhead to inserts and deletes. And many people believe that there is no benefit to foreign keys beyond referential integrity (which, the application can handle perfectly well, right?). But is that an accurate statement? Here's the basis for our investigation, a query in AdventureWorks2008R2: SELECT p.LastName + ', ' + p.FirstName AS 'PersonName' FROM Person.Address AS a JOIN Person.BusinessEntityAddress AS bea ON a.AddressID = bea.AddressID JOIN Person.BusinessEntity AS be ON bea.BusinessEntityID = be.BusinessEntityID JOIN Person.Person AS p ON be.BusinessEntityID = p.BusinessEntityID; This query results in the following execution plan: I know that is an ugly query and an ugly query plan, but bear with me for a moment. Do you…
Read More