Build Polymorphic Associations in SQL Server with Foreign Keys

By: Jared Westover | Updated: 2024-12-06 | Comments | Related: > Database Design

Problem

Do you like a challenge? If you answered yes, you're my kind of person. Recently, a developer presented me with a problem: they needed a foreign key reference in one table to associate with multiple other tables. Over the years, I've often been asked how to make this situation work. However, achieving this relationship with foreign keys is technically impossible with SQL Server and most mainstream relational database platforms. Since SQL Server restricts foreign keys to referencing a single table, how can we solve this problem?

Solution

In this article, we'll look at what's often called polymorphic associations in SQL Server. I'll start with a brief overview of this special relationship and a simple example to illustrate the concept. Then, we'll explore a few design methods for establishing this uncommon relationship. By the end, you'll know how to handle this challenge in your database—or at least recognize it by name.

Exploring Foreign Keys

Let's start with a definition of foreign keys. And what better source than Microsoft, the creators of SQL Server? Microsoft defines foreign keys as a column or combination of columns used to establish and enforce a link between the data in two tables and control the data that can be stored in the foreign key table.

We primarily use foreign keys to help enforce referential integrity. Without them, you risk issues with orphaned or inconsistent data. Foreign keys can also help performance by allowing join elimination, where SQL skips redundant joins based on known relationships. Have you ever tried to purge data from dozens of tables without primary and foreign key relationships? If so, you know it can be a huge pain.

Diving into Polymorphic Relationships

You might wonder: What exactly is a polymorphic relationship? Simply put, this relationship allows one table to be associated with multiple other tables, each representing a different type of record.

An example I would like to use involves three tables:

Movies - Stores information about films.
VideoGames - Contains records about my favorite video games.
Reviews - Where I store reviews for both video games and movies.

Follow along with the code below.

-- mssqltips.com
USE [master];
GO

IF DB_ID('MediaReviewsDemo') IS NOT NULL
BEGIN
    ALTER DATABASE MediaReviewsDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE MediaReviewsDemo;
END;
GO

CREATE DATABASE MediaReviewsDemo;
GO

ALTER DATABASE MediaReviewsDemo SET RECOVERY SIMPLE;
GO

USE MediaReviewsDemo;
GO

CREATE TABLE dbo.Movies (
    MovieId INT NOT NULL,
    Title NVARCHAR(256) NOT NULL,
    Director NVARCHAR(256) NOT NULL,
    Runtime INT NOT NULL,
    ReleaseDate DATE NULL,
    CONSTRAINT PK_Movies_MovieId PRIMARY KEY CLUSTERED (MovieId)
);

INSERT INTO dbo.Movies (MovieId, Title, Director, Runtime, ReleaseDate) VALUES
    (1, 'Blade', 'Stephen Norrington', 120, '1998-08-12'),
    (2, 'Mortal Kombat', 'Paul W. S. Anderson', 101, '1995-07-13'),
    (3, 'Bloodsport', 'Newt Arnold', 92, '1988-02-26');

CREATE TABLE dbo.VideoGames (
    VideoGameId INT NOT NULL,
    Title NVARCHAR(256) NOT NULL,
    Developer NVARCHAR(256) NOT NULL,
    Console NVARCHAR(256) NOT NULL,
    Multiplayer BIT NOT NULL,
    ReleaseDate DATE NULL,
    CONSTRAINT PK_VideoGames_VideoGameId PRIMARY KEY CLUSTERED (VideoGameId)
);

INSERT INTO dbo.VideoGames (VideoGameId, Title, Developer, Console, Multiplayer, ReleaseDate) VALUES
    (1, 'Super Street Fighter II Turbo', 'Capcom', 'SNES', 1, '1993-08-01'),
    (2, 'Mortal Kombat II', 'Midway Games', 'SNES', 1, '1993-09-10'),
    (3, 'Super Mario Bros. 3', 'Nintendo', 'NES', 1, '1990-02-10');

CREATE TABLE dbo.Reviews (
    ReviewId INT NOT NULL,
    MediaId INT NOT NULL,
    MediaType NVARCHAR(50) NOT NULL,
    ReviewContent NVARCHAR(MAX) NULL,
    Rating INT NOT NULL,
    CONSTRAINT PK_Reviews_ReviewId PRIMARY KEY CLUSTERED (ReviewId)
);

INSERT INTO dbo.Reviews (ReviewId, MediaId, MediaType, ReviewContent, Rating) VALUES
    (1, 3, 'Movie', 'The Best Van Damme movie ever!', 10);
GO

It's important to note that the Movies and VideoGames tables contain vastly different attributes, even though they are both entities that can have a review. If these tables had similar attributes, I'd consider skipping my suggestions below and combining them into one table. The Reviews table contains the same attributes regardless of the media type. In Reviews, we add an indicator attribute to specify the media type the review is for.

One massive benefit of this design pattern is that it simplifies your schema by reducing the number of tables you need to create. Another is its extensibility. Suppose we needed to add another table, such as Books or Music. No problem—just add a new media type to the Reviews table.

However, the drawback is that you can't define a foreign key reference to multiple tables. Without a foreign key constraint or an equivalent, our schema's referential integrity is at stake. Before long, someone will start inserting zeros to represent screwy business logic. We've all seen these crazy workarounds in real-world scenarios.

Workarounds

In the following sections, I'll present three workarounds to keep our foreign key relationships intact. I'm intentionally leaving out triggers as a solution. While some people don't like triggers, due to performance or hidden logic, adding one is better than compromising data integrity.

Multiple Indicator Type Columns

One of the simplest ways to solve this problem is to add an indicator column for each media type in the Reviews table. Since a foreign key reference can be NULL in SQL Server, we shouldn't encounter any problems unless our code stops populating them—but wait, there's a workaround for that.

Below is a code example of how this setup might look.

-- mssqltips.com
DROP TABLE IF EXISTS dbo.Reviews;
CREATE TABLE dbo.Reviews (
    ReviewId INT NOT NULL,
    MovieId INT NULL,
    VideoGameId INT NULL,
    ReviewContent NVARCHAR(MAX) NULL,
    Rating INT NOT NULL,
    CONSTRAINT PK_Reviews_ReviewId PRIMARY KEY CLUSTERED (ReviewId),
    CONSTRAINT FK_Reviews_MovieId FOREIGN KEY (MovieId) REFERENCES dbo.Movies (MovieId),
    CONSTRAINT FK_Reviews_VideoGameId FOREIGN KEY (VideoGameId) REFERENCES dbo.VideoGames (VideoGameId),
    CONSTRAINT CHK_OnlyOneMediaType CHECK (
        (MovieId IS NOT NULL AND VideoGameId IS NULL) OR
        (MovieId IS NULL AND VideoGameId IS NOT NULL)
    )
);
GO

INSERT INTO dbo.Reviews (ReviewId, MovieId, VideoGameId, ReviewContent, Rating) VALUES
    (1, 3, NULL, 'The Best Van Damme movie ever!', 10);
GO

SELECT 
    m.Title,
    r.Rating,
    r.ReviewContent
FROM 
    dbo.Movies m
    INNER JOIN dbo.Reviews r ON m.MovieId = r.MovieId;
GO

Output:

This solution is scalable if we don't plan to add too many additional indicator types. However, if the Reviews table is highly volatile (i.e., frequent inserts, updates, and deletes), we could encounter issues with excessive locking. Additionally, this approach may pose problems if we need a soft delete mechanism on Reviews, but the Media tables don't use a similar design. For example, suppose Reviews uses a soft delete flag to mark records as inactive without physically deleting them, but Movies and VideoGames hard-delete the records. In that case, we must NULL the references in the Reviews table.

I've also added a check constraint to ensure that either VideoGameId or MovieId is populated, but not both. The code example below will fail.

-- mssqltips.com
INSERT INTO dbo.Reviews (ReviewId, MovieId, VideoGameId, ReviewContent, Rating) VALUES
    (2, 3, 2, 'The Best Van Damme movie ever!', 10);
GO

Output:

Msg 547, Level 16, State 0, Line 144
The INSERT statement conflicted with the CHECK constraint "CHK_OnlyOneMediaType". The conflict occurred in database "MediaReviewsDemo", table "dbo.Reviews".

Multiple Association Tables

Another easy workaround is to add multiple review tables, such as MovieReviews and VideoGameReviews. However, this multiple-table method can get out of hand if you have dozens of types. I've included a code sample below.

-- mssqltips.com
CREATE TABLE dbo.VideoGameReviews (
    ReviewId INT NOT NULL,
    VideoGameId INT NOT NULL,
    ReviewContent NVARCHAR(MAX) NULL,
    Rating INT NOT NULL,
    CONSTRAINT PK_VideoGameReviews_ReviewId PRIMARY KEY CLUSTERED (ReviewId),
    CONSTRAINT FK_VideoGameReviews_VideoGameId FOREIGN KEY (VideoGameId) REFERENCES dbo.VideoGames (VideoGameId)
);
GO

CREATE TABLE dbo.MovieReviews (
    ReviewId INT NOT NULL,
    MovieId INT NOT NULL,
    ReviewContent NVARCHAR(MAX) NULL,
    Rating INT NOT NULL,
    CONSTRAINT PK_MovieReviews_ReviewId PRIMARY KEY CLUSTERED (ReviewId),
    CONSTRAINT FK_MovieReviews_MovieId FOREIGN KEY (MovieId) REFERENCES dbo.Movies (MovieId)
);
GO

The primary drawback here is redundancy. In our example, the review tables are identical except for the foreign key references. Also, if we want to add a new column to the Reviews, we need to update multiple tables. Two more drawbacks are needing more joins and likely performing a UNION every time you query for reviews. Despite these table design drawbacks, this is a solid approach.

Quasi-bridge Table

Bridge tables are commonly referenced when dealing with many-to-many joins in data warehousing situations or using a reporting tool like Power BI. A bridge table holds a unique combination of keys between two or more tables.

A typical bridge table might look something like the example below.

We can apply the same concept here to solve our problem. However, this is not your typical bridge table—I call it a quasi-bridge table.

-- mssqltips.com
CREATE TABLE dbo.BridgeReviews (
    ReviewId INT NOT NULL,
    MovieId INT NULL,
    VideoGameId INT NULL,
    CONSTRAINT PK_BridgeReviews PRIMARY KEY (ReviewId),
    CONSTRAINT FK_BridgeReviews_Reviews FOREIGN KEY (ReviewId) REFERENCES dbo.Reviews (ReviewId),
    CONSTRAINT FK_BridgeReviews_Movies FOREIGN KEY (MovieId) REFERENCES dbo.Movies (MovieId),
    CONSTRAINT FK_BridgeReviews_VideoGames FOREIGN KEY (VideoGameId) REFERENCES dbo.VideoGames (VideoGameId),
    CONSTRAINT CHK_BridgeReviews_OneMediaType CHECK (
        (MovieId IS NOT NULL AND VideoGameId IS NULL) OR
        (MovieId IS NULL AND VideoGameId IS NOT NULL)
    )
);
GO

In this example, we still have Movies, VideoGames, and Reviews, but our Reviews table doesn't store the foreign key relationship. Instead, the BridgeReviews table acts as the control center, and the Reviews table focuses solely on storing the review data.

The quasi-bridge table's main downside is the data model's added complexity. This complexity impacts querying (extra joins) and any front-end code, such as an ORM that performs CRUD operations. Also, we are still not adding the foreign keys to the Reviews table. While the quasi-bridge table is an option, it's likely my least favorite.

Clean Up

If you're done with testing, remember to run the code below to delete the demo database.

-- mssqltips.com
USE [master];
GO

SET NOCOUNT ON;

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'MediaReviewsDemo')
BEGIN
    ALTER DATABASE MediaReviewsDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE MediaReviewsDemo;
END;
GO

Summary

So, which option is the best? The answer, as usual, is that it depends. The solution will depend on your current schema and how much you want to complicate the lives of your application developers. If the parent tables are identical in attributes, I would likely try to combine them into one table. On the other hand, if you only intend to have two parent tables, you might consider creating two review tables or adding a column in the Reviews table for each parent. As mentioned above, don't dismiss using a trigger to enforce this relationship.

I'd like to hear your thoughts on the three solutions above. Do you prefer one or have a different solution in mind?

Next Steps

Did you know foreign keys allow SQL Server to eliminate unnecessary table joins? Bert Wagner wrote an insightful article, "Join Elimination: When SQL Server Removes Unnecessary Tables."
Are you looking for a primer on foreign keys in SQL Server? Then check out Jan Potgieter's article, "Create SQL Server Tables Using Foreign Keys for Referential Integrity."
Christina Blake's article, "How to Join Many to Many with a Bridge Table in Power BI," provides an excellent overview of many-to-many joins and using bridge tables.

About the author

Jared Westover is a passionate technology specialist at Crowe, helping to build data solutions. For the past two decades, he has focused on SQL Server, Azure, Power BI, and Oracle. Besides writing for MSSQLTips.com, he has published 12 Pluralsight courses about SQL Server and Reporting Services. Jared enjoys reading and spending time with his wife and three sons when he's not trying to make queries go faster.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2024-12-06