T-SQL中的相对约束和权限

引言

本文介绍了如何使用影子表和表触发器在 T-SQL 中实现相对数据库约束和权限设置。

背景

在关系数据库中，通常可以定义以下约束：

• 非空 (Not null)
• 主键
• 外键 (Foreign key)
• 唯一 (Unique)
• Check

这些约束大多数是绝对的，因为它们的定义适用于为其定义的表中的所有数据行。例如，不能根据同一行中相同或不同列的值来定义列为可空/非空或唯一/非唯一。此类约束是相对的，因为它们的定义取决于实际存储的数据值（请参阅“ER 数据模型中的相对约束”，Behm/Teorey 1993）。

ArticleDB 示例

为了说明相对约束的概念，让我们来看一个商品数据库的示例，该数据库存储了家用电器（例如冰箱、洗衣机、搅拌机、吸尘器）及其配件（例如搅拌机杯、吸尘器袋）。在此数据库中，应存储商品的以下信息：

• 商品编号 (Article number)
• 助记符 (Mnemonic)
• 名称
• 描述
• 功耗 (Power consumption)
• 类型

类型用于区分电子电器和配件。

从业务角度看，应实施以下约束：

1. ArticleNumber 唯一标识一个商品，所有商品都必须填充 ArticleNumber。
2. Mnemonic 是可选的，但必须以 A（用于电器）或 P（用于配件）开头。此外，该代码必须是唯一的，即如果设置了代码，则任何两个商品不应具有相同的代码。
3. PowerConsumption 对电器是强制的，对配件是可选的。
4. 配件必须链接到一个电器，但电器则不必链接到任何配件。

使用 T-SQL 支持的约束，这将转化为以下 Article 表。

CREATE TABLE Article(
    Number int NOT NULL,
    Mnemonic varchar(20) NULL,
    Name varchar(100) NOT NULL,
    Description varchar(255) NULL,
    PowerConsumption decimal(12, 4) NULL,
    LinkedArticle int NULL,
    Type char(1) NOT NULL,
CONSTRAINT pk_Article PRIMARY KEY (Number),
CONSTRAINT chk_Type CHECK (Type IN ('A','P')),
CONSTRAINT chk_Mnemonic CHECK ((Mnemonic like 'A%' AND Type = 'A') OR (Mnemonic LIKE 'P%' AND Type = 'P')),
CONSTRAINT chk_PowerConsumption CHECK ((PowerConsumption IS NOT NULL AND Type ='A') OR Type ='P'),
CONSTRAINT chk_LinkedArticle CHECK ((LinkedArticle IS NOT NULL AND Type ='P') OR Type ='A')
)
GO
 
ALTER TABLE Article 
ADD CONSTRAINT fk_LinkedArticle FOREIGN KEY (LinkedArticle) REFERENCES Article (Number)
GO

第一个约束是通过表上的主键定义来实现的。其余的通过在列和表上定义的约束来处理。由于 T-SQL（以及大多数其他 RDBMS）无法定义包含同一表其他记录的约束，因此第四个约束只能定义到 LinkedArticle 必须为配件设置，但不能定义它引用的文章必须是电器。第三个约束也会引起问题，因为 Mnemonic 列不能声明为唯一，因为这将意味着只有一个行可以为 NULL，而这是我们不想要的。应该允许多个 Mnemonic 设置为 NULL 的行。

确保 LinkedArticle 仅从配件引用电器的其中一种方法是创建两个表，以便单独存储电器和配件，然后在它们之间定义外键约束。然而，此解决方案会带来一些缺点：

• 如果数据库模型要通过 Orders 表进行扩展，那么定义一个引用所有商品的`外键约束`将无法轻松实现。
• 需要采取机制来保持两个表之间的商品编号唯一。
• 想要访问所有商品的查询将需要查询两个表或通过 union 视图。

虽然上述几点并非不可能解决，但在处理拆分成两个表的 Article 表时会很不方便，而为确保外键正确性所付出的努力可能不值得。此外，拆分 Article 表仍然无法解决 Mnemonic 列的唯一性问题，并且允许同时存在多个 NULL 值。

另一种可以解决这两个问题的方案是创建影子表，这些表会从主表 Article 自动填充，并在其上定义相对约束。为了演示此方法，针对给定示例，设置了三个表：一个用于配件，一个用于电器，一个用于助记符。

CREATE TABLE Article(
    Number int NOT NULL,
    Mnemonic varchar(20) NULL,
    Name varchar(100) NOT NULL,
    Description varchar(255) NULL,
    PowerConsumption decimal(12, 4) NULL,
    LinkedArticle int NULL,
    Type char(1) NOT NULL,
CONSTRAINT pk_Article PRIMARY KEY (Number),
CONSTRAINT chk_Type CHECK (Type IN ('A','P'))
)
GO
 
ALTER TABLE Article 
ADD CONSTRAINT fk_LinkedArticle_Number 
    FOREIGN KEY (LinkedArticle) REFERENCES Article (Number)
GO
 
CREATE TABLE sdw_Mnemonic (
    Number int NOT NULL,
    Code varchar(20) NOT NULL,
CONSTRAINT pk_Mnemonic PRIMARY KEY (Number),
CONSTRAINT unq_Mnemonic UNIQUE (Code))
GO
 
ALTER TABLE sdw_Mnemonic 
ADD CONSTRAINT fk_Mnemonic_Number FOREIGN KEY (Number) REFERENCES Article (Number) 
ON UPDATE CASCADE
ON DELETE CASCADE
GO
 
CREATE TABLE sdw_Appliance (
    Number int NOT NULL,
    Mnemonic varchar(20) NULL,
    Name int NOT NULL,
    Description int NULL,
    PowerConsumption int NOT NULL,
    LinkedArticle int NULL,
Type char(1) not NULL,
CONSTRAINT pk_Appliance PRIMARY KEY (Number),
CONSTRAINT chk_MnemonicA CHECK (Mnemonic LIKE 'A%'),
CONSTRAINT chk_LinkedArticleA CHECK(LinkedArticle IS NULL),
CONSTRAINT chk_TypeA CHECK (Type = 'A'))
GO
 
ALTER TABLE sdw_Appliance 
ADD CONSTRAINT fk_Appliance_Number FOREIGN KEY (Number) REFERENCES Article (Number) 
ON UPDATE CASCADE 
ON DELETE CASCADE
GO
 
CREATE TABLE sdw_AccessoryPart (
    Number int NOT NULL,
    Mnemonic varchar(20) NULL,
    Name int NOT NULL,
    Description int NULL,
    PowerConsumption int NULL,
    LinkedArticle int NOT NULL,
    Type char(1) NOT NULL,
CONSTRAINT pk_AccessoryPart PRIMARY KEY (Number),
CONSTRAINT chk_MnemonicP CHECK (Mnemonic like 'P%'),
CONSTRAINT chk_TypeP CHECK (Type = 'P'))
GO
 
ALTER TABLE sdw_AccessoryPart
ADD CONSTRAINT fk_AccessoryPart_Number FOREIGN KEY (Number) REFERENCES Article (Number)
ON UPDATE CASCADE 
ON DELETE CASCADE
GO
 
ALTER TABLE sdw_AccessoryPart
ADD CONSTRAINT fk_AccessoryPart_LinkedArticle FOREIGN KEY 
        (LinkedArticle) REFERENCES sdw_Appliance (Number)
GO

前缀 sdw_ 用于指示它们是影子表。

为了从 Article 填充影子表，定义了一个触发器，该触发器使用 merge 语句使主表 Article 中的数据与影子表保持同步。

CREATE TRIGGER tiu_Article ON Article FOR INSERT, UPDATE
AS
BEGIN
    --Merge Mnemnonic

    MERGE 
        INTO sdw_Mnemonic AS s
        USING inserted AS I
        ON s.Number = i.Number
        WHEN MATCHED AND i.Mnemonic IS NOT NULL AND i.Mnemonic <> s.Code
            THEN UPDATE SET s.Code = i.Mnemonic
        WHEN MATCHED AND i.Mnemonic IS NULL
            THEN DELETE
        WHEN NOT MATCHED AND i.Mnemonic IS NOT NULL
            THEN INSERT (Number, Code) VALUES (i.Number, i.Mnemonic);
 
    --Merge Appliance

    MERGE 
        INTO sdw_Appliance AS s
        USING inserted AS I
        ON s.Number = i.Number
        WHEN MATCHED AND i.Type = 'A'
            THEN UPDATE SET Mnemonic = i.Mnemonic,
                Name = len(i.Name), 
                Description = len(i.Description), 
                PowerConsumption = datalength(i.PowerConsumption), 
                LinkedArticle = i.LinkedArticle
        WHEN MATCHED AND i.Type = 'P'
            THEN DELETE
        WHEN NOT MATCHED AND i.Type = 'A'
            THEN INSERT (Number, Mnemonic, Name, 
              Description, PowerConsumption, LinkedArticle, Type) 
            VALUES (i.Number, i.Mnemonic, len(i.Name), len(i.Description), 
                    datalength(i.PowerConsumption), i.LinkedArticle, i.Type);
 
    --Merge AccessoryPart

    MERGE 
        INTO sdw_AccessoryPart AS s
        USING inserted AS I
        ON s.Number = i.Number
        WHEN MATCHED AND i.Type = 'P'
            THEN UPDATE SET Mnemonic = i.Mnemonic,
                Name = len(i.Name), 
                Description = len(i.Description), 
                PowerConsumption = datalength(i.PowerConsumption), 
                LinkedArticle = i.LinkedArticle
        WHEN MATCHED AND i.Type = 'A'
            THEN DELETE
        WHEN NOT MATCHED AND i.Type = 'P'
            THEN INSERT (Number, Mnemonic, Name, Description, 
              PowerConsumption, LinkedArticle, Type) 
            VALUES (i.Number, i.Mnemonic, len(i.Name), len(i.Description), 
                    datalength(i.PowerConsumption), i.LinkedArticle, i.Type);
END
GO

使用标准 SQL 的替代方法是：

CREATE TRIGGER tiu_Article ON Article FOR INSERT, UPDATE
AS
BEGIN
    --INSERT Mnemnonic
    INSERT INTO sdw_Mnemonic (Number, Code)
        SELECT i.Number, i.Mnemonic
    FROM inserted i
    WHERE i.Mnemonic IS NOT NULL 
    AND NOT EXISTS(SELECT 1 FROM sdw_Mnemonic WHERE Number = i.Number)
 
    --UPDATE Mnemnonic
    UPDATE sdw_Mnemonic 
        SET Code = i.Mnemonic
    FROM sdw_Mnemonic s
    JOIN inserted i ON i.Number = s.Number
    WHERE i.Mnemonic IS NOT NULL
    AND i.Mnemonic <> s.Code
 
    --DELETE Mnemnonic
    DELETE sdw_Mnemonic
    FROM sdw_Mnemonic s
    JOIN inserted i ON i.Number = s.Number 
    WHERE Mnemonic IS NULL
 
    --INSERT Appliance
    INSERT INTO sdw_Appliance (Number, Mnemonic, Name, 
                Description, PowerConsumption, LinkedArticle, Type)
        SELECT i.Number, i.Mnemonic, LEN(i.Name), LEN(i.Description), 
               DATALENGTH(i.PowerConsumption), i.LinkedArticle, i.Type
    FROM inserted i
    WHERE i.Type = 'A' 
    AND NOT EXISTS(SELECT 1 FROM sdw_Appliance WHERE Number = i.Number)
 
    --Update Appliance
    UPDATE sdw_Appliance 
        SET Mnemonic = i.Mnemonic, 
            Name = LEN(i.Name), 
            Description = LEN(i.Description), 
            PowerConsumption = DATALENGTH(i.PowerConsumption), 
            LinkedArticle = i.LinkedArticle
    FROM sdw_Appliance s
    JOIN inserted i ON i.Number = s.Number
    WHERE i.Type = 'A'
 
    --DELETE Appliance
    DELETE sdw_Appliance
    FROM sdw_Appliance s
    JOIN inserted i ON i.Number = s.Number
    WHERE i.Type = 'P'
 
    --INSERT AccessoryPart
    INSERT INTO sdw_AccessoryPart (Number, Mnemonic, Name, 
           Description, PowerConsumption, LinkedArticle, Type)
    SELECT i.Number, i.Mnemonic, LEN(i.Name), LEN(i.Description), 
           DATALENGTH(i.PowerConsumption), i.LinkedArticle, i.Type
    FROM inserted i
    WHERE i.Type = 'P'
    AND NOT EXISTS(SELECT 1 FROM sdw_AccessoryPart WHERE Number = i.Number)
 
    --UPDATE AccessoryPart
    UPDATE sdw_AccessoryPart 
        SET Mnemonic = i.Mnemonic,
            Name = LEN(i.Name), 
            Description = LEN(i.Description), 
            PowerConsumption = DATALENGTH(i.PowerConsumption), 
            LinkedArticle = i.LinkedArticle
    FROM sdw_AccessoryPart s
    JOIN inserted i ON i.Number = s.Number
    WHERE i.Type = 'P'
 
    --DELETE AccessoryPart
    DELETE sdw_AccessoryPart
    FROM sdw_AccessoryPart s
    JOIN inserted i ON i.Number = s.Number
    WHERE i.Type = 'A'
END
GO

派生影子表的规则是：

• 为主表中需要应用相对约束的数据子集创建影子表。
• 在影子表上定义约束。
• 创建触发器将主表中的数据填充到影子表中。

这种方法的缺点是会产生数据冗余。为了在一定程度上缓解这个问题，可以使用以下策略：

• 不要将主表中的完整数据写入影子表：可以通过仅存储数据长度（如本例所示），或仅在影子表中存储相关列（即已定义约束的列），或两者的组合。
• 将影子表集成到您的审计概念中：如果您已经在数据库中使用影子表来保留更改历史记录（旧记录存储在审计表中），则可以使用这些表来定义相对约束，而不是设置专门的影子表。

相对权限设置

通过为影子表分配不同的权限，也可以实现相对权限的概念。传统上，只能在表或列级别授予权限，而不能在数据行级别授予。

在我们的示例中，现在有可能为电器和配件分配不同的权限，并实现基本的相对权限设置，这意味着可以设置一些用户只能维护 Article 中电器的数据，而另一些用户只能维护 Article 中配件的数据。

使用代码

要使用代码，请先在数据库（例如 tempdb）中设置对象，然后尝试运行一些语句。下面是一些测试约束的语句。

--Prepare test data
INSERT INTO Article (Number, Mnemonic, Name, Description, PowerConsumption, LinkedArticle, Type)
VALUES (1000, 'AFRID', 'Fridge', 'Fridge 200 Watts', 200, NULL, 'A')
INSERT INTO Article (Number, Mnemonic, Name, Description, PowerConsumption, LinkedArticle, Type)
VALUES (2000, 'ABLEN', 'Blender', 'Blender 50 Watts', 50, NULL, 'A')
INSERT INTO Article (Number, Mnemonic, Name, Description, PowerConsumption, LinkedArticle, Type)
VALUES (3000, 'PCUP', 'Blender cup', 'Cup for blender', NULL, 2000, 'P')
INSERT INTO Article (Number, Mnemonic, Name, Description, PowerConsumption, LinkedArticle, Type)
VALUES (4000, 'AVAC','Vacuum cleaner','Vacuum cleaner 2000 Watts',2000, null, 'A')
INSERT INTO Article (Number, Mnemonic, Name, Description, PowerConsumption, LinkedArticle, Type)
VALUES (5000, 'PNOZ','Nozzle','Vacuum cleaner nozzle 10 Watt',10, 4000, 'P')
GO
 
--Check that Mnemonic starts with A for appliances and P for accessory parts

UPDATE Article
SET Mnemonic = 'FRID'
WHERE Number = 1000
GO
 
Output: The MERGE statement conflicted with the CHECK constraint "chk_MnemonicA". 
  The conflict occurred in database "tempdb", table "dbo.sdw_Appliance", column 'Mnemonic'.
 
UPDATE Article
SET Mnemonic = 'CUP'
WHERE Number = 3000
GO
 
Output: The MERGE statement conflicted with the CHECK constraint "chk_MnemonicP". 
  The conflict occurred in database "tempdb", table "dbo.sdw_AccessoryPart", column 'Mnemonic'.
 
--Check that Mnemonic is either NULL or unique

UPDATE Article
SET Mnemonic = NULL
GO
 
Output: (No error)
 
UPDATE Article
SET Mnemonic = Type
GO
 
Output: Violation of UNIQUE KEY constraint 'unq_Mnemonic'. Cannot insert 
  duplicate key in object 'dbo.sdw_Mnemonic'. The duplicate key value is (A).
 
--Check that PowerConsumption is mandatory for appliances and optional for accessory parts

UPDATE Article
SET PowerConsumption = NULL
WHERE Type = 'A'
GO
 
Output: Cannot insert the value NULL into column 'PowerConsumption', 
  table 'tempdb.dbo.sdw_Appliance'; column does not allow nulls. UPDATE fails.
 
WHERE Number = 5000
UPDATE Article
SET PowerConsumption = NULL
GO
 
Output: (No error)
 
--Check that accessory parts must be linked
--to an appliance but appliances must not have any link

UPDATE Article
SET LinkedArticle = NULL
WHERE Type = 'P'
GO
 
Output: Cannot insert the value NULL into column 'LinkedArticle', 
  table 'tempdb.dbo.sdw_AccessoryPart'; column does not allow nulls. UPDATE fails.
 
UPDATE Article
SET LinkedArticle = 3000
WHERE Number = 5000
GO
 
Output: The MERGE statement conflicted with the FOREIGN KEY constraint "fk_AccessoryPart_LinkedArticle". 
  The conflict occurred in database "tempdb", table "dbo.sdw_Appliance", column 'Number'.
 
UPDATE Article
SET LinkedArticle = 1000
WHERE Number = 4000
GO
 
Output: The MERGE statement conflicted with the CHECK constraint "chk_LinkedArticleA". 
  The conflict occurred in database "tempdb", 
  table "dbo.sdw_Appliance", column 'LinkedArticle'.

关注点

我最初使用 Sybase T-SQL 编写本文的示例，但很快意识到由于缺乏对级联更新和删除的支持，我不得不切换到 MS T-SQL。

可能存在更好的方法来建模商品数据库，但选择此方法的目的是使模型尽可能包含各种相对约束的示例。

也可能存在更好的方法来使用继承表来实现相对约束，并将不同约束的列移到子表中。然而，我所知道的 T-SQL（Sybase 和 MS SQL）不支持继承。因此，在 Oracle PL 或 PostgresSQL 中看到解决方案将很有趣。

历史

• 2013 年 2 月 6 日：初稿。
• 2013 年 5 月 12 日：提交初稿。