如何跨繼承層次結構中的一對一關係強制數據一致性

考慮一下典型學校數據庫的這種設計：

Person:
-----------------    
FirstName
LastName
SocialSecurityNumber
Phone
Email

Student:
-----------------
Grade

Teacher:
-----------------
Specialty

如您所見，此設計中有三個表。一個表包含有關抽象的一般資訊，另外兩個表包含有關具體實體的特定數據。Students並且表與Teachers表具有一對一的關係Persons。到目前為止沒有問題。

但是，基於這種設計，我們有可能在Personstable 中有一條 ID 為 30 的記錄，並且由於任何原因（例如針對數據庫手動執行腳本），我們在Students, 和Teachers表中插入了兩條具有相同 ID 的記錄。這樣，ID 為 30 的人既是老師，又是學生。

好吧，這在學校數據庫的背景下是有道理的。但是在某些情況下，派生表是互斥的，因此來自一種具體類型的實體在邏輯上也不能是來自相反類型的實體。

如何防止在分層數據庫設計中跨派生表插入重疊 ID？我知道我可以使用觸發器來實現這一點，但我認為使用觸發器時程式碼有異味。

注意：我使用的是 SQL Server，Entity Framework 將此設計表稱為每個類型 (TPT)。

將學生/教師屬性添加到 Person。由於此屬性依賴於 Person 中的鍵（無論是什麼），因此任何人都不能既是教師又是學生。現在的問題是保證不會將類型為學生的人添加到教師等。

對於在檢查約束中支持查詢的 DBMS:s，您可以執行以下操作：

ALTER TABLE Student Add constraint ...
        CHECK ( (select type 
                 from person p
                 where p.<key> = <key>) = 'Student' )

如果您的 DBMS 不支持這種類型的構造，您可以在 Person 中添加一個由主鍵 + 類型屬性組成的超級鍵。將 type 屬性添加到 Teacher 和 Student，添加一個檢查約束以保證這些“子表”中的類型，以及一個包含 type 屬性的外鍵：

ALTER TABLE Person ADD COLUMN type_attribute varchar(..) not null;
ALTER TABLE Person ADD CONSTRAINT ... UNIQUE (<key>, type_attribute);

ALTER TABLE Student ADD COLUMN type_attribute varchar(..) not null;
ALTER TABLE Student ADD CONSTRAINT ... CHECK (type_attribute = 'Student')
ALTER TABLE Student ADD CONSTRAINT ... 
    FOREIGN KEY (<key>, type_attribute)
    REFERENCES Person (<key>, type_attribute);

現在無法將學生添加為教師，反之亦然。必須通過添加資訊的交易來保證具有該屬性的人確實有學生/老師。

一種方法是引入 PersonType 屬性。在 Student 和 Teacher 表中將其用作複合鍵以及檢查約束和外鍵將確保給定人員的行僅存在於其中一個實體和正確的表中。我不能說這個實現與 EF 相處得有多好。

使用備用鍵（唯一約束）也有類似的方法；外鍵可以引用唯一約束/索引列，而不僅僅是主鍵的列。

CREATE TABLE dbo.PersonType(
     PersonTypeCode char(1) NOT NULL
       CONSTRAINT PK_PersonType PRIMARY KEY
   , TypeName varchar(30) NOT NULL
   );

INSERT INTO dbo.PersonType (PersonTypeCode, TypeName)
   VALUES('S', 'Student'), ('T', 'Teacher');

CREATE TABLE dbo.Person(
     PersonID int NOT NULL
   , PersonTypeCode char(1) NOT NULL
       CONSTRAINT FK_Person_PersonType
       FOREIGN KEY REFERENCES dbo.PersonType(PersonTypeCode)
   , FirstName varchar(50) NOT NULL
   , LastName varchar(50) NOT NULL
   , SocialSecurityNumber char(11) NOT NULL
       CONSTRAINT UN_Person_SocialSecurityNumber UNIQUE
   , Phone varchar(20) NOT NULL
   , Email nvarchar(255) NOT NULL
   , CONSTRAINT PK_Person
       PRIMARY KEY(PersonID, PersonTypeCode)
   );

CREATE TABLE dbo.Student(
     PersonID int NOT NULL
   , PersonTypeCode char(1) NOT NULL DEFAULT 'S'
       CONSTRAINT CK_Student_PersonType CHECK (PersonTypeCode = 'S')
   , Grade char(2) NOT NULL
   , CONSTRAINT PK_Student 
       PRIMARY KEY(PersonID, PersonTypeCode)
   , CONSTRAINT FK_Student_Person
       FOREIGN KEY(PersonID, PersonTypeCode)
       REFERENCES dbo.Person(PersonID, PersonTypeCode)
   );

CREATE TABLE dbo.Teacher(
     PersonID int NOT NULL
   , PersonTypeCode char(1) NOT NULL DEFAULT 'T'
       CONSTRAINT CK_Teacher_PersonType CHECK (PersonTypeCode = 'T')
   , Specialty varchar(20) NOT NULL
   , CONSTRAINT PK_Teacher 
       PRIMARY KEY(PersonID, PersonTypeCode)
   , CONSTRAINT FK_Teacher_Person
       FOREIGN KEY(PersonID, PersonTypeCode)
       REFERENCES dbo.Person(PersonID, PersonTypeCode)
   );

引用自：https://dba.stackexchange.com/questions/95891