如何创建合并Student和Teacher表的单视图实现UserId唯一约束

核心思路 #

使用UNION ALL纵向合并两张表的全量数据，显式指定所有列，对两张表的专属字段在另一侧补同类型NULL值，新增bit类型标识列区分人员身份；同时给视图加SCHEMABINDING绑定，满足SQL Server索引视图的创建要求，最后在视图上创建UserId的唯一索引实现全局唯一约束。

视图创建代码 #

CREATE OR ALTER VIEW VI_Person 
WITH SCHEMABINDING -- 索引视图必填配置，绑定底层表架构防止随意修改
AS
    -- 查询学生数据
    SELECT 
        Id,
        UserId,
        FirstName,
        DisplayName,
        LastName,
        Gender,
        BirthDate,
        CAST(NULL AS nvarchar(10)) AS Abbreviation, -- 教师专属字段，学生侧补同类型空值
        CAST(1 AS bit) AS IsStudent -- 身份标识：1=学生，0=教师
    FROM dbo.Student -- 架构绑定要求必须指定表的架构前缀，默认架构为dbo

    UNION ALL

    -- 查询教师数据
    SELECT 
        Id,
        UserId,
        FirstName,
        DisplayName,
        LastName,
        CAST(NULL AS int) AS Gender, -- 学生专属字段，教师侧补同类型空值
        CAST(NULL AS date) AS BirthDate, -- 学生专属字段，教师侧补同类型空值
        Abbreviation,
        CAST(0 AS bit) AS IsStudent
    FROM dbo.Teacher
GO

创建唯一索引实现UserId全局约束 #

SQL Server要求索引视图必须先创建唯一聚集索引，执行以下语句即可实现UserId全局唯一校验，同一个UserId同时关联学生和教师身份时会直接触发约束报错：

CREATE UNIQUE CLUSTERED INDEX IX_VI_Person_UserId
ON VI_Person(UserId)
GO

原有写法问题说明 #

• 逗号连接两表写法：本质是笛卡尔积查询，会生成大量无效关联数据，且存在重复列名，完全不符合行合并需求
• 第一版UNION写法：上下两段查询的列数、字段顺序、数据类型完全不匹配，无法执行
• FULL JOIN写法：是按条件做列关联，不是行合并，无法返回两表全量字段和全量数据
• 第二版UNION写法：只查询了两表共有的3个字段，未补全专属字段、未新增身份标识列，不满足需求

内容的提问来源于stack exchange，提问作者Davidbeh