Обеспечение исключительности в наследовании таблиц: составной внешний ключ против проверочного ограничения

Question

Я следую методике, описанной в " Реализации наследования таблиц в SQL Server" Джеффа Смита (который, по-видимому, является де-факто подходом для реализации такого рода структуры). People Базовая таблица имеет отношение 1: 0..1 с тремя таблицами подтипов. Students, Teachers, Parents, Это традиционно делается путем определения первичного ключа таблиц подтипов в качестве внешнего ключа базовой таблицы.

Для обеспечения исключительности среди подтипов (не позволяя одному и тому же человеку быть одновременно учеником и учителем), автор рекомендует добавить PersonTypeID как постоянный вычисляемый столбец для каждой из таблиц подтипов и включение его в ограничение внешнего ключа с базовой таблицей.

CREATE TABLE PersonType 
(
    PersonTypeID INT PRIMARY KEY,
    Description  VARCHAR(10)
);

INSERT INTO PersonType 
VALUES (1, 'Student'),
       (2, 'Teacher'),
       (3, 'Parent');

CREATE TABLE People 
( 
    PersonID     INT PRIMARY KEY,
    PersonTypeID INT REFERENCES PersonType (PersonTypeID), 
    Name         VARCHAR(10),
    UNIQUE (PersonID, PersonTypeID)
)

CREATE TABLE Students 
( 
    PersonID INT PRIMARY KEY, 
    PersonTypeID AS 1 PERSISTED, -- student 
    EnrollmentDate DATETIME, 
    FOREIGN KEY (PersonID, PersonTypeID) REFERENCES People (PersonID, PersonTypeID) 
) 

CREATE TABLE Teachers 
( 
    PersonID INT PRIMARY KEY, 
    PersonTypeID AS 2 PERSISTED, -- teacher 
    HireDate DATETIME, 
    FOREIGN KEY (PersonID, PersonTypeID) REFERENCES People (PersonID, PersonTypeID) 
) 

CREATE TABLE Parents 
( 
    PersonID INT PRIMARY KEY, 
    PersonTypeID AS 3 PERSISTED, -- parents 
    DifficultyScore INT, 
    FOREIGN KEY (PersonID, PersonTypeID) REFERENCES People (PersonID, PersonTypeID) 
) 

Тем не менее, этот подход страдает от ряда проблем:

1. Это тратит впустую дополнительный столбец места на каждой из таблиц подтипа.
2. Требуется дополнительное уникальное ограничение на базовую таблицу. Это тратит больше места (поскольку он будет реализован как уникальный индекс) и замедляет обновления базовой таблицы.
3. Ограничение внешнего ключа включает проверку двух столбцов (вместо одного), что замедляет обновление таблиц подтипов.

Моя гипотеза состоит в том, что было бы лучше обеспечить уникальность, используя вместо этого проверочные ограничения через скалярную функцию. Это позволило бы израсходовать неиспользуемое хранилище для дополнительного столбца и уникального индекса, ускорить обновления базовой таблицы и, как мы надеемся, достигнуть той же производительности для обновлений таблиц подтипов, что и составной внешний ключ.

CREATE TABLE People 
( 
    PersonID     INT PRIMARY KEY,
    PersonTypeID INT REFERENCES PersonType (PersonTypeID), 
    Name         VARCHAR(10)
)

CREATE FUNCTION GetPersonTypeID (@PersonID INT)
RETURNS INT
AS
BEGIN
    RETURN 
    (
        SELECT PersonTypeID 
        FROM People
        WHERE PersonID = @PersonID
    )
END;

CREATE TABLE Students 
( 
    PersonID INT PRIMARY KEY REFERENCES People (PersonID) 
        CHECK (dbo.GetPersonTypeID(PersonID) = 1),
    EnrollmentDate DATETIME
) 

CREATE TABLE Teachers 
( 
    PersonID INT PRIMARY KEY REFERENCES People (PersonID)
        CHECK (dbo.GetPersonTypeID(PersonID) = 2),
    HireDate DATETIME
) 

CREATE TABLE Parents 
( 
    PersonID INT PRIMARY KEY REFERENCES People (PersonID)
        CHECK (dbo.GetPersonTypeID(PersonID) = 3),
    DifficultyScore INT
) 

Есть ли причина, по которой этот подход не следует использовать?

Answer 1

Дополнительное хранилище на самом деле минимально, если ваш столбец типа персоны является крошечным (например, до 255 типов людей), вы по-прежнему используете только один дополнительный байт на человека. Так что это не должно быть серьезным фактором при принятии решения, основная проблема в том, что скалярные udf-файлы работают значительно хуже, чем ограничения внешнего ключа. Это было проверено, и результаты, показанные в статье Скалярные пользовательские функции, заключенные в ограничения CHECK, очень медленные и могут завершиться ошибкой для многорядных обновлений.

Тестирование также включено в этот ответ SO