浅谈数据库完整性

数据库完整性（Database Integrity）是指数据库中数据在逻辑上的一致性、正确性、有效性和相容性。数据库完整性由各种各样的完整性约束来保证，因此可以说数据库完整性设计就是数据库完整性约束的设计。

关系数据库的完整性规则是数据库设计的重要内容。接下来介绍一下数据库的完整性。

实体完整性

实体完整性指的是表中元组（行）的完整性，为了保证元组之间的非空和唯一性。主要通过设置表的主键来约束表的非空和唯一性或者通过使用唯一值约束。

当创建主键约束时，如果表的存储引擎是innoDB，系统默认会在所在的列和列组合上建立对应的唯一索引。多列组合的主键约束，列都不允许为空值，并且组合的值不允许重复。每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。插入的记录会根据主键的值的顺序排放。

--创建列级主键
CREATE TABLE STU(
    SID INT AUTO_INCREMENT PRIMARY KEY,
    SNAME VARCHAR(20),
    SAGE INT DEFAULT 22,
    SGENDER CHAR(6)
);

--创建表级主键
CREATE TABLE TEACHER(
    TID INT NOT NULL,
    TNAME VARCHAR(20),
    TAGE INT DEFAULT 22,
    TGENDER CHAR(6),
    ID_CARD CHAR(18),
    PRIMARY KEY(TID,ID_CARD)
);

--创建唯性来约束
CREATE TABLE CLASSROOM(
    CID INT NOT NULL UNIQUE,
    CNAME VARCHAR(20),
    CGRADE INT
);

--创建复合唯一性约束
CREATE TABLE CLASSROOM(
    CID INT NOT NULL,
    CNAME VARCHAR(20),
    CGRADE INT,
    CONSTRAINT UNID UNIQUE(CID)
);

参照完整性

参照完整性（Referential Integrity）属于表间规则。对于永久关系的相关表，在更新、插入或删除记录时，如果只改其一，就会影响数据的完整性。如删除父表的某记录后，子表的相应记录未删除，致使这些记录称为孤立记录。对于更新、插入或删除表间数据的完整性，统称为参照完整性。通常，在客观现实中的实体之间存在一定联系，在关系模型中实体及实体间的联系都是以关系进行描述，因此，操作时就可能存在着关系与关系间的关联和引用。

在关系数据库中，关系之间的联系是通过公共属性实现的。这个公共属性经常是一个表的主键，同时是另一个表的外键。参照完整性体现在两个方面：实现了表与表之间的联系，外键的取值必须是另一个表的主键的有效值，或是“空”值。同样外键仅有InnoDB支持。

CREATE TABLE TEACHER(
    TID INT NOT NULL,
    TNAME VARCHAR(20),
    TAGE INT DEFAULT 22,
    TGENDER CHAR(6),
    ID_CARD CHAR(18),
    CID INT,
    PRIMARY KEY(TID,ID_CARD),
    FOREIGN KEY(CID) REFERENCES CLASSROOM(CID)
);

对参照表或被参照表进行增删改查操作时，都可能会无意识的破坏参照完整性，因此提供了三种处理这种违规操作的处理。

1. NO ACTION（拒绝执行）默认策略
2. CASCADE（级联操作）当删除或修改被参照表的一个元组导致与参照表不一致时，同时删除或修改参照表中所有相关元组。
3. SET NULL（设置为空）当删除或修改被参照表的一个元组导致与参照表不一致时，将参照表中所有相关的元组的对应属性值设为空值。

CREATE TABLE TEACHER(
    TID INT NOT NULL,
    TNAME VARCHAR(20),
    TAGE INT DEFAULT 22,
    TGENDER CHAR(6),
    ID_CARD CHAR(18),
    CID INT,
    PRIMARY KEY(TID,ID_CARD),
    FOREIGN KEY(CID) REFERENCES CLASSROOM(CID)
    ON DELETE CASCADE,
    ON UPDATE SET NULL,
);

用户自定义完整性

在建立表时定义属性的同时定义约束条件：UNIQUE,NOT NULL,CHECK短语。

--创建列级约束条件
CREATE TABLE TEACHER(
    TID INT NOT NULL,
    TNAME VARCHAR(20),
    TAGE INT DEFAULT 22 CHECK(TAGE BETWEEN 0 AND 120),
    TGENDER CHAR(6) CHECK(TGENDER IN('MALE','FEMALE','UNKNOWN')),
    ID_CARD CHAR(18),
    CID INT,
    PRIMARY KEY(TID,ID_CARD),
    FOREIGN KEY(CID) REFERENCES CLASSROOM(CID)
    ON DELETE CASCADE,
    ON UPDATE SET NULL,
);

--创建表级约束条件
CREATE TABLE TEACHER(
    TID INT NOT NULL,
    TNAME VARCHAR(20),
    TAGE INT DEFAULT 22,
    TGENDER CHAR(6),
    ID_CARD CHAR(18),
    CID INT,
    PRIMARY KEY(TID,ID_CARD),
    FOREIGN KEY(CID) REFERENCES CLASSROOM(CID)
    ON DELETE CASCADE,
    ON UPDATE SET NULL,
    CHECK(TGENDER='MALE' OR TNAME NOT LIKE 'Mr.%')
);

完整性约束命名子句

通过constraint来为一个约束命名。

CREATE TABLE TEACHER(
    TID INT NOT NULL,
    TNAME VARCHAR(20),
    TAGE INT DEFAULT 22 CONSTRAINT CAGE CHECK(TAGE BETWEEN 0 AND 120),
    TGENDER CHAR(6),
    ID_CARD CHAR(18),
    CID INT,
    PRIMARY KEY(TID,ID_CARD),
    FOREIGN KEY(CID) REFERENCES CLASSROOM(CID)
    ON DELETE CASCADE,
    ON UPDATE SET NULL,
);

域中的完整性约束条件

通过CREATE DOMAIN

CREATE DOMAIN AGEDOMAIN INT CONSTRAINT CAGE CHECK(VALUE BETWEEN 0 AND 120);