Polymorphic Associations: Why One Column Cannot Point at Many Tables

Polymorphic associations let one column point at many tables, and that is exactly why the database cannot protect them. A foreign key needs one parent table. Give each parent its own nullable key and a CHECK constraint, and SQL Server enforces the rule for you.

Why one column cannot point at many tables

Picture a junior DBA asking why a report shows a comment for an order that does not exist. The comments table has two columns, EntityType and EntityId. The type says “Order” or “Customer”, and the id says which one. It looks flexible. It is also a trap.

A foreign key points at exactly one table. It cannot read the text in EntityType and decide which table to check. So SQL Server accepts whatever you type. The demo below makes two real parents, then inserts four comments. Two are good, one points at an order that is not there, and one has a typo in the type.

DROP TABLE IF EXISTS dbo.CommentsPoly;
DROP TABLE IF EXISTS dbo.Comments;
DROP TABLE IF EXISTS dbo.Customers;
DROP TABLE IF EXISTS dbo.Orders;

CREATE TABLE dbo.Orders (Id int PRIMARY KEY);
CREATE TABLE dbo.Customers (Id int PRIMARY KEY);
INSERT dbo.Orders VALUES (1);
INSERT dbo.Customers VALUES (1);

CREATE TABLE dbo.CommentsPoly
(
    CommentId  int PRIMARY KEY,
    EntityType varchar(20) NOT NULL,
    EntityId   int NOT NULL
);
INSERT dbo.CommentsPoly VALUES
    (1, 'Order', 1), (2, 'Customer', 1),
    (3, 'Order', 999), (4, 'Custmer', 1);

SELECT * FROM dbo.CommentsPoly ORDER BY CommentId;

All four rows go in without a complaint. Nothing is wrong as far as the engine knows.

Find the orphans by hand

With this design, you have to hunt for bad rows yourself. Check each supported type separately, and report types you do not know. Do not use an inner join here. It quietly drops the missing parents, and a missing parent is what you are looking for. Keep CommentId in the output so you can trace the repair.

SELECT CommentId, EntityType, EntityId, Problem
FROM (
    SELECT c.CommentId, c.EntityType, c.EntityId,
           CASE
             WHEN c.EntityType = 'Order'
              AND NOT EXISTS (SELECT 1 FROM dbo.Orders AS o WHERE o.Id = c.EntityId)
                  THEN 'Order not found'
             WHEN c.EntityType = 'Customer'
              AND NOT EXISTS (SELECT 1 FROM dbo.Customers AS u WHERE u.Id = c.EntityId)
                  THEN 'Customer not found'
             WHEN c.EntityType NOT IN ('Order', 'Customer')
                  THEN 'Unknown type'
           END AS Problem
    FROM dbo.CommentsPoly AS c
) AS x
WHERE Problem IS NOT NULL
ORDER BY CommentId;

You get comment 3 with “Order not found” and comment 4 with “Unknown type”. Every new parent type means another branch in this query. Miss one, and the orphans stay hidden.

Give each parent its own key

The fix is plain. Add one nullable column per parent, and put a real foreign key on each. NULL means “this comment does not belong to that parent”. Then add a CHECK that exactly one of the columns is filled in. Without the CHECK, a comment could belong to two parents or to none.

CREATE TABLE dbo.Comments
(
    CommentId  int PRIMARY KEY,
    OrderId    int NULL,
    CustomerId int NULL,
    FOREIGN KEY (OrderId)    REFERENCES dbo.Orders (Id),
    FOREIGN KEY (CustomerId) REFERENCES dbo.Customers (Id),
    CHECK ((CASE WHEN OrderId    IS NULL THEN 0 ELSE 1 END)
         + (CASE WHEN CustomerId IS NULL THEN 0 ELSE 1 END) = 1)
);
INSERT dbo.Comments VALUES (1, 1, NULL), (2, NULL, 1);

SELECT * FROM dbo.Comments ORDER BY CommentId;

Try to break it

Now throw three bad rows at the table. One has no parent. One has two parents. One points at order 999, which does not exist. Each insert fails with error 547. The CHECK catches the first two, and the foreign key catches the third.

BEGIN TRY
    INSERT dbo.Comments VALUES (3, NULL, NULL);
END TRY
BEGIN CATCH
    SELECT 'No parent' AS Test, ERROR_NUMBER() AS ErrorNumber;
END CATCH;

BEGIN TRY
    INSERT dbo.Comments VALUES (4, 1, 1);
END TRY
BEGIN CATCH
    SELECT 'Two parents' AS Test, ERROR_NUMBER() AS ErrorNumber;
END CATCH;

BEGIN TRY
    INSERT dbo.Comments VALUES (5, 999, NULL);
END TRY
BEGIN CATCH
    SELECT 'Missing parent' AS Test, ERROR_NUMBER() AS ErrorNumber;
END CATCH;

SELECT COUNT(*) AS AcceptedComments FROM dbo.Comments;

The screenshot shows the result of this block together with the previous one. Two valid comments stay in the table, and all three bad ones are refused.

What happens when a new parent type shows up

This design is not free. A third parent, say Invoice, needs another column, another foreign key, and a changed CHECK. Some people dislike that. I like it. The cost is visible in a script review, instead of hiding in bad data.

If you have many parent types, use a separate comments table per parent instead, such as OrderComments and CustomerComments. Reporting then needs a UNION, but every table has one clean foreign key. Pick based on how many parents you expect and how you report.

Application checks help, but they do not replace constraints. The next writer might be an import script or a quick fix in SSMS. Test the rules on your own tables with a few bad rows, like the ones above. This demo creates only tables in your current database, and the last block drops them.

DROP TABLE IF EXISTS dbo.CommentsPoly;
DROP TABLE IF EXISTS dbo.Comments;
DROP TABLE IF EXISTS dbo.Customers;
DROP TABLE IF EXISTS dbo.Orders;

Decide who the parent is first, then let the database hold everyone to it.

A relationship is not just an identifier, it is a rule the database can enforce.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.