sql server 跨表w唯一约束

原创

mob649e8160b585 2024-09-08 06:46:16 ©著作权

文章标签 触发器 Server SQL 文章分类 SQL Server 数据库

©著作权归作者所有：来自51CTO博客作者mob649e8160b585的原创作品，请联系作者获取转载授权，否则将追究法律责任

SQL Server 跨表唯一约束科普

在数据库设计中，维护数据一致性和完整性是一项重要的工作。唯一约束（Unique Constraint）确保在特定列上的数据是唯一的，防止重复数据的产生。但在复杂的数据模型中，可能会出现需要跨多个表来保持数据唯一性的需求。这篇文章将探讨如何在 SQL Server 中实现跨表唯一约束，并提供代码示例。

一、基本概念

在 SQL Server 中，唯一约束用于确保某列（或多列）中的值不能重复。这在表内非常简单，但当涉及到多个表时，情况会变得复杂。例如，考虑两个表：Users 和 Emails。每个用户可以有多个电子邮件，但每个电子邮件地址必需是唯一的，即不能被其他用户重复。

数据模型关系图

我们可以用以下的 mermaid 代码描绘表之间的关系：

erDiagram
    USERS {
        INT id PK
        STRING username
    }
    EMAILS {
        INT id PK
        STRING email
        INT user_id FK
    }
    
    USERS ||--o{ EMAILS : has

二、实现跨表唯一约束

在 SQL Server 中，虽然不能直接在一个表上定义跨表的唯一约束，但可以通过触发器（Triggers）来实现这一功能。触发器是一种特殊的存储过程，它在插入、更新或删除数据时自动执行。我们将创建一个触发器来确保电子邮件地址在 Emails 表中是唯一的。

步骤

创建 Users 和 Emails 表。
创建触发器来检查插入或更新操作。

代码示例

首先，创建两个表：

CREATE TABLE Users (
    id INT PRIMARY KEY IDENTITY,
    username NVARCHAR(50) NOT NULL
);

CREATE TABLE Emails (
    id INT PRIMARY KEY IDENTITY,
    email NVARCHAR(100) NOT NULL,
    user_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES Users(id)
);

接下来，创建触发器以检查电子邮件的唯一性：

CREATE TRIGGER trg_CheckUniqueEmail
ON Emails
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT 1
        FROM Emails e
        JOIN inserted i ON e.email = i.email
        GROUP BY e.email
        HAVING COUNT(*) > 1
    )
    BEGIN
        RAISERROR('Email must be unique across all users.', 16, 1);
        ROLLBACK TRANSACTION;
    END
END;

触发器工作原理

在上述代码中，我们创建了一个触发器 trg_CheckUniqueEmail，该触发器会在新记录插入或已存在记录更新后自动执行。触发器使用 inserted 伪表来访问更新或插入的数据，当发现相同的电子邮件地址在 Emails 表中出现多次时，触发器将抛出错误并回滚事务。

三、序列图示例

为了更好地理解触发器的工作流程，我们可以用序列图来表示触发器是如何工作的。

sequenceDiagram
    participant U as User
    participant D as Database
    participant T as Trigger

    U->>D: Insert Email
    D->>T: Trigger Execution
    T->>D: Check for duplicates
    alt Duplicate Found
        T-->>D: Raise Error
        D-->>U: Rollback Transaction
    else No Duplicate
        D-->>U: Commit Transaction
    end