Disjoint subtyping is a scenario that is often encountered in data modeling. In one frequently used modeling approach, an entity of a certain type is represented by a database table, and each subtype of this entity is represented by another table. Subtyping is disjoint if an instance of a type corresponds to at most one instance of a subtype. For example, we may have a table named Animal, and three other tables named Extinct, Living, and Mythical (perhaps some would argue that these are not really disjoint, but let’s ignore this for now). In this example, these three tables represent entities that are disjoint subtypes of the Animal type.
When implementing disjoint subtyping in SQL, it is necessary to enforce the rule that for each row in the type table, there is one related row in at most one subtype table. It is possible to implement this rule declaratively by creative use of foreign key and check constraints, as described by David Portas in his blog.
The other day it occurred to me that one could simplify this implementation a little bit. Rather than add single-value subtype columns to each of the subtype tables, each such column with a check constraint and a default constraint, one could use SQL Server computed columns. This way, there is no need to declare the check and the default constraints, and the column could be ignored for all intents and purposes, while still enforcing the disjointness data integrity rule.
With this modification, David’s schema becomes as follows (change highlighted):
CREATE TABLE Products
(SKU INT NOT NULL PRIMARY KEY,
ProductType CHAR(1) NOT NULL
CHECK (ProductType IN (‘B’,‘C’,‘D’ /* Book, CD or DVD */)),
Title VARCHAR(50) NOT NULL,
UNIQUE (SKU,ProductType));
CREATE TABLE Books
(SKU INT NOT NULL PRIMARY KEY,
ProductType AS ISNULL(CAST(‘B’ AS CHAR(1)), ”) PERSISTED,
Pages SMALLINT NOT NULL,
FOREIGN KEY (SKU,ProductType) REFERENCES Products (SKU,ProductType));
(I’m omitting the CDs and DVDs tables for brevity.)
Note that the computed column still needs to be persisted – SQL Server will refuse to create the foreign key otherwise – so this approach can only be used with SQL Server 2005 and later. I also had to explicitly cast the column to match the data type of the referenced column. The ISNULL() function makes the column not nullable in the table’s metadata. Since the column actually cannot have NULLs, this might avoid some confusion.
Update 2009-10-19: Here is a Connect suggestion to add that support in T-SQL.
© 2019 Microsoft. All rights reserved.