Allow constant references in foreign key constraints
as Won't Fix
10/19/2009 11:39:02 AM
This suggestion is to allow constants, in addition to column names, in the definition of foreign key constraints. For example:
ALTER TABLE Table1
ADD CONSTRAINT fk1 FOREIGN KEY (Col1, 'A') REFERENCES Table2 (Col1, Col2);
One important use case for this is declarative integrity enforcement when modeling entity subtypes. As an example, consider a data model for a store that sells Books, CDs, and DVDs, which are all subtypes of Product. The data model has four tables: Product, Book, CD, DVD. The constraint to be enforced in this case is that a product can be only of one subtype (disjoint subtyping), thus a row in Product can only be referenced by a row in one of the other three tables. With the proposed syntax, it would be possible to to model this as follows:
CREATE TABLE Product (
ProductID int PRIMARY KEY,
ProductType char(1) NOT NULL CHECK (ProductType IN ('B','C','D')),
CONSTRAINT skProduct1 UNIQUE (ProductID, ProductType)
CREATE TABLE Book (
BookID int PRIMARY KEY,
CONSTRAINT fkBookProduct FOREIGN KEY (BookID, 'B') REFERENCES Product (ProductID, ProductType)
and similar for the two other tables.
Non-disjoint and overlapping subtyping can be modeled similarly, with the required subtyping constraint implemented as a CHECK constraint on the main entity type table.
See http://www.idi.ntnu.no/~heggland/cv/ForeignSuperkeys.pdf for detailed discussion and additional use cases.
Presently, one can only implement this approach to modeling entity subtyping by introducing actual type differentiator columns (perhaps as persisted computed columns) in each table corresponding to a subtype, which is much less efficient and robust compared to the proposed change in allowed T-SQL syntax.