sql数据库完整性在哪里设置

为了在SQL数据库中设置完整性，可以在（1）表级别、（2）列级别、（3）触发器三个层级进行设置。 其中，表级别的设置包括定义主键、外键、唯一键等。具体来说，如果你希望确保某个表中的特定列在所有行中具有唯一值，可以设置该列为主键或唯一键。这不仅能提高数据查询的效率，还能保证数据的一致性和完整性，有效避免数据重复和逻辑冲突，比如多个用户使用相同的用户ID这样的情况。

一、表级别完整性设置

表级别完整性设置包括主键、外键和唯一键等。 为了保证每一行记录在表中的唯一性，可以设置主键。主键列中的每个值必须是唯一的，不能包含NULL值。例如，在一个用户信息表中，用户ID可以作为主键。创建一个主键的方法如下：

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    UserName VARCHAR(50),
    UserEmail VARCHAR(50)
);

外键用于保证两个表之间的关系完整性。它确保了一个表中的值必须存在于另一个表中。例如，订单表中的用户ID必须是用户信息表中存在的用户ID。创建外键的方法如下：

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    UserID INT,
    FOREIGN KEY (UserID) REFERENCES Users(UserID)
);

唯一键确保表中的某列数据在所有记录中都是唯一的，类似于主键，但一个表可以有多个唯一键。设置唯一键的方法如下：

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) UNIQUE,
    Price DECIMAL(10, 2)
);

二、列级别完整性设置

列级别完整性设置包括NOT NULL、DEFAULT、CHECK等约束。 这些约束用于确保单个列中的数据符合特定条件。NOT NULL约束确保列中每条记录的值不能为NULL，这对于字段如电子邮件地址和用户名等非常重要。设置NOT NULL的方法如下：

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(50) NOT NULL,
    CustomerEmail VARCHAR(50) NOT NULL
);

默认值约束(DEFAULT)用于在插入新记录时，如果没有提供列的值，数据库将自动插入默认值。例如，可以为“注册日期”列提供当前日期作为默认值：

CREATE TABLE Registrations (
    RegistrationID INT PRIMARY KEY,
    RegistrationDate DATE DEFAULT GETDATE(),
    UserID INT
);

CHECK约束用于确保列中的数据符合特定条件。例如，可以确定“年龄”列的值必须在0到150之间：

CREATE TABLE Persons (
    PersonID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT CHECK (Age BETWEEN 0 AND 150)
);

三、触发器完整性设置

触发器是数据库中一种特殊的存储过程，它会在特定事件发生时自动执行。 触发器可用于实施复杂的完整性规则，如日志记录和复杂条件检查。例如，在插入数据到订单表时，可以设置触发器检查库存是否足够：

CREATE TRIGGER trgCheckStock
ON Orders
AFTER INSERT
AS
BEGIN
    DECLARE @ProductID INT, @OrderQuantity INT
    SELECT @ProductID=ProductID, @OrderQuantity=Quantity FROM inserted
    IF EXISTS (SELECT * FROM Products WHERE ProductID=@ProductID AND Stock < @OrderQuantity)
    BEGIN
        RAISERROR ('Insufficient stock', 16, 1)
        ROLLBACK TRANSACTION
    END
END

触发器不仅可以在插入数据时执行，还可以在更新和删除数据时执行，以确保数据的完整性和一致性。例如，可以创建一个触发器来记录删除操作：

CREATE TRIGGER trgAuditDelete
ON Employees
AFTER DELETE
AS
BEGIN
    INSERT INTO AuditLog (EmployeeID, Action, ActionDate)
    SELECT EmployeeID, 'DELETE', GETDATE() FROM deleted
END

四、综合使用完整性设置

为了确保数据完整性，通常需要综合使用表级别、列级别和触发器等多种手段。 例如，在一个复杂的订单管理系统中，可能需要以下设置：

表级别完整性：设置主键、外键和唯一键，确保数据唯一性和表间关系。
列级别完整性：使用NOT NULL、DEFAULT和CHECK等约束，确保每一列数据符合预期。
触发器完整性：实现复杂的业务逻辑和数据检查，自动记录数据变更和操作日志。

典型案例分析

假设我们有一个电商系统，需要在多个层级设置数据完整性。首先，在用户表中，我们可以设置用户ID为主键，保证唯一性：

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    UserName VARCHAR(50) UNIQUE,
    UserEmail VARCHAR(50) NOT NULL,
    RegistrationDate DATE DEFAULT GETDATE()
);

接下来，在订单表中，我们将用户ID设置为外键，确保订单都与有效用户相关联：

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE DEFAULT GETDATE(),
    UserID INT,
    FOREIGN KEY (UserID) REFERENCES Users(UserID)
);

为了保证商品的库存正确性，我们在插入订单时设置触发器，检查库存是否充足：

CREATE TRIGGER trgCheckStock
ON Orders
AFTER INSERT
AS
BEGIN
    DECLARE @ProductID INT, @OrderQuantity INT
    SELECT @ProductID=ProductID, @OrderQuantity=Quantity FROM inserted
    IF EXISTS (SELECT * FROM Products WHERE ProductID=@ProductID AND Stock < @OrderQuantity)
    BEGIN
        RAISERROR ('Insufficient stock', 16, 1)
        ROLLBACK TRANSACTION
    END
END

最后，为了记录订单的取消操作，我们设置一个触发器记录删除操作，以便审计和追踪：

CREATE TRIGGER trgAuditDelete
ON Orders
AFTER DELETE
AS
BEGIN
    INSERT INTO AuditLog (OrderID, Action, ActionDate)
    SELECT OrderID, 'DELETE', GETDATE() FROM deleted
END

通过这种综合使用表级别、列级别和触发器的完整性设置方法，可以确保电商系统中的数据一致性和正确性，防止数据错误和逻辑冲突。