#TIL About Foreign Key Scripts

No matter how long you work with something, you can always find something that you never knew before. I found one about foreign keys this week.

I was reviewing SQL scripts for coworkers and I noticed that the foreign keys were written without referencing the parent table’s column. But the script didn’t fail and created the foreign keys correctly. So how did this work?

Let’s take a look at these two tables:

CREATE TABLE test1 (
TestID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
TestValue VARCHAR(10)
)
GO
CREATE TABLE test2 (
TestTwoID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
TestIDFK INT,
TestValueFK VARCHAR(10)
)
GO

Now let’s create a foreign key from test2 to test1:

ALTER TABLE test2
ADD CONSTRAINT FK_TestID FOREIGN KEY (TestIDFK)
REFERENCES test1
GO

Notice that I’m not specifying which column on test1 the foreign key is referencing. When I look at the constraint I just created using sp_helpconstraint, I see this (hiding some of the columns that aren’t relevant for this conversation):

constraint_type	constraint_name	constraint_keys
FOREIGN KEY	FK_TestID	TestIDFK
		REFERENCES AdventureWorks2016.dbo.test1 (TestID)

Foreign keys can reference either a primary key or a unique constraint. Test1 only had one option, the primary key column TestID, so SQL Server was able to figure it out.

Now, what would happen if there was a unique constraint on test1? Let’s create a unique constraint on test1.TestValue:

ALTER TABLE test1
ADD CONSTRAINT UQ_Test UNIQUE (TestValue)
GO

Let’s try to create a foreign key on test2.TestValueFK. Ideally, this would go to test1.TestValue since the data types are the same. What will SQL Server try to do? Let’s use this foreign key statement:

ALTER TABLE test2
ADD CONSTRAINT FK_TestValue FOREIGN KEY (TestValueFK)
REFERENCES test1
GO

Here’s what SQL Server returned:

Msg 1778, Level 16, State 0, Line 27
Column 'test1.TestID' is not the same data type as referencing 
column 'test2.TestValueFK' in foreign key 'FK_TestValue'.
Msg 1750, Level 16, State 1, Line 27
Could not create constraint or index. See previous errors.

Even though there were two options, SQL Server tried to create the foreign key to the primary key constraint.

But when you specify the column name like this:

ALTER TABLE test2
ADD CONSTRAINT FK_TestValue FOREIGN KEY (TestValueFK)
       REFERENCES test1 (TestValue)
GO

Here’s the results:

Commands completed successfully.

Now what happens if you have more than one column in the primary key? Let’s look at these two tables:

CREATE TABLE test3 (
       TestIntPK     int NOT NULL,
       TestStrPK     varchar(10) NOT NULL,
       CONSTRAINT pk_test3 PRIMARY KEY (TestIntPK, TestStrPK)
)
GO

CREATE TABLE test4 (
       TestFourPK    int IDENTITY(1,1) NOT NULL PRIMARY KEY,
       TestFourInt   int,
       TestFourStr   varchar(10)
)
GO

Now let’s try to create a foreign key, leaving out the primary key columns in the statement:

ALTER TABLE test4
ADD CONSTRAINT FK_CompoundKeys 
       FOREIGN KEY (TestFourInt, TestFourStr)
       REFERENCES test3
GO

Here’s the result:

Commands completed successfully.

It worked!!!!

constraint_type	constraint_name	constraint_keys
FOREIGN KEY	FK_CompoundKeys	TestFourInt, TestFourStr
		REFERENCES AdventureWorks2016.dbo.test3 (TestIntPK, TestStrPK)

So as long as you are going to the primary keys and list the fields properly, SQL Server figures out what’s supposed to go where.

Now that you know you can do this, the question is: Should we?

On the one hand, if you are referencing the primary key of a table, any script you write without specifying the primary key columns will work. If it works, it doesn’t really matter.

But for me, the purpose of writing a script isn’t just for deploying the database. It has the same purpose as commenting your code – so you can look at the script and know exactly what it’s doing. I may not know the table offhand so I don’t know what the primary key column is. But it’s right there in the script so I don’t have to extra work.

Even if it has nothing to do with your database performance, it’s a little thing you can do to document your database for others.