Return to Revisions

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.