The cost of using a simple synthetic integer PK is small, and the benefit in your case would probably be quite considerable.
- As you point out, you'll have a much simpler FK relationship.
- A small PK makes for small (and fast) indices. Your total table space will probably be made less by adding such a column.
- If business rules ever change, you won't have to reorder the table.
The only material downside that comes to mind is that you may lose performance on queries which benefited from clustering on the composite PK. If you think that's likely to be significant, than continue clustering on the composite candidate key, but put the PK on the synthetic key.
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.
Best Answer
Quote from the documentation:
Although this may come as a surprise, it is "documented behaviour". This is probably one of these cases where you/we must just accept that things have been implemented in a certain manner ie InnoDB have implemented foreign keys this way.
Whether the design (in question) makes sense or not depends on the underlying business rules. There may well be situations that require such a constraint.