Sql-server – Foreign key constraint based on conditional column

database-designMySQLsql server

I'm trying to figure out the best way to handle this situation:

I have a table set up as follows:

LEARNING CONTEXT
================
LearningContextID - INT - Surrogate Primary Key
EntityID          - INT - Foreign Key to an Entity Table
OutcomeID         - INT - Foreign Key to an Outcome Table
Context           - VARCHAR - Name of the Entity Table

I have 3 tables:
University, Program, and Course which are all "entities".

How can I set up a foreign key constraint that will know which table to be linked to? I suppose it's a composite key of EntityID and Context, but how can I make that conditionally constrain against the given Context's table?

Best Answer

How can I set up a foreign key constraint that will know which table to be linked to?

Either you can't, or you shouldn't. :)

The standard way to structure this is to have a parent Entity table, and a table-per-child for each of University, Program, and Course. The Entity table optionally contains a denormalized column that specifies the type of entity -- this has a bunch of advantages, so I recommend it. Also, at least in the development stages, I would recommend creating triggers to validate that rows in this structure end up in the right place (i.e., for a given EntityID, there should be only 1 matching row in all the child tables).

Then all you do is create the foreign key to the primary key of Entity.

What I've described looks like this (SQL Server syntax):

CREATE TABLE dbo.EntityType
(
    EntityTypeID tinyint NOT NULL
        PRIMARY KEY
);

CREATE TABLE dbo.Entity
(
    EntityID int NOT NULL IDENTITY
        PRIMARY KEY,
    EntityTypeID tinyint NOT NULL
        FOREIGN KEY REFERENCES dbo.EntityType(EntityTypeID)
);

CREATE TABLE dbo.University
(
    EntityID int NOT NULL
        PRIMARY KEY
        FOREIGN KEY REFERENCES dbo.Entity(EntityID)
);

CREATE TABLE dbo.LearningContext
(
    LearningContextID int NOT NULL IDENTITY
        PRIMARY KEY,
    EntityID int NOT NULL
        FOREIGN KEY REFERENCES dbo.Entity(EntityID)
);

This is the equivalent of subclassing in a database, if you're familiar with client-side programming constructs.

Note how this structure easily allows adding a new derived Entity subtype without changing the existing schema objects.

Related Question