1、实验目的

1. 熟悉通过SQL对数据进行完整性控制。

2. 完成书本上习题的上机练习。

2、实验平台KingbaseES

KingbaseES及其交互式查询工具ISQLW。

3、实验内容和要求

因为完整性约束大部分是在定义表结构时进行的，因此可能多次定义表，如果表名重复，先将旧表删除再建立新表

3.1 实体完整性

实体完整性要求每个数据表都必须有主键，而作为主键的所有字段，其属性必须是独一及非空值。

关系模型的实体完整性在CREATE TABLE中用PRIMARY KEY定义。定义主码的方法分为定义为列级约束条件和定义为表级约束条件。

定义表Student，将其中的Sno属性定义为主码：

CREATE TABLE Student(
    Sno CHAR(7) PRIMARY KEY,
    Sname CHAR(8) NOT NULL,
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20));

也可以：

CREATE TABLE Student(
    Sno CHAR(7) ,
    Sname CHAR(8) NOT NULL,
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20),
    PRIMARY KEY(Sno));

对于由多个属性构成的码，只能够将其定义为表级约束条件，而无法用列级约束条件来实现。

定义表SC，将其中的Sno，Cno属性定义为主码

CREATE TABLE SC(
    Sno CHAR(7) NOT NULL,
    Cno CHAR(4) NOT NULL,
    Grade SMALLINT,
    PRIMARY KEY(Sno,Cno));

3.2 参照完整性

参照的关系中的属性值必须能够在被参照关系找到或者取空值，否则不符合数据库的语义。

关系模式的参照完整性是在CREATE TABLE中用FOREIGN KEY语句来定义的，并用REFERENCES来指明外码参照的是哪些表的主码。

定义表SC，其中Sno参照表Student的主码Sno，Cno参照表Course的主码Cno。

CREATE TABLE SC(
    Sno CHAR(7) NOT NULL,
    Cno CHAR(4) NOT NULL,
    Grade SMALLINT,
    PRIMARY KEY(Sno,Cno),
    FOREIGN KEY(Sno) REFERENCES Student(Sno),
    FOREIGN KEY(Cno) REFERENCES Course(Cno));

3.3 用户自定义完整性

用户自定义完整性指针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。

列值非空：

在定义SC表时，Sno、Cno和Grade属性都不允许取空值。在不特别声明的情况下，非码属性的值是允许取空值的。

CREATE TABLE SC(
    Sno CHAR(7) NOT NULL,
    Cno CHAR(4) NOT NULL,
    Grade SMALLINT NOT NULL);

列值唯一：

建立部门表Dept，要求部门名称Dname取值唯一，部门编号属性Deptno为主码。

CREATE TABLE Dept(
    Deptno NUMERIC(7) PRIMARY KEY,
    Dname VARCHAR(9) UNIQUE,
    Loc VARCHAR(10));

CHECK短语：

CHECK短语指定列值应该满足的条件。

定义表Student，属性Ssex的值只允许取“男”或者“女”。

CREATE TABLE Student(
    Sno CHAR(7) PRIMARY KEY,
    Sname CHAR(8) NOT NULL,
    Ssex CHAR(2) CHECK(Ssex IN('男','女')),  /*CHECK语句约束条件*/
    Sage SMALLINT,
    Sdept CHAR(20));

定义表SC，属性Grade的值定义在[0,100]之间。

CREATE TABLE SC(
    Sno CHAR(7) NOT NULL,
    Cno CHAR(4) NOT NULL,
    Grade SMALLINT CHECK (Grade>=0 AND Grade<=100),
    PRIMARY key(Sno,Cno));

用户定义的元组上的约束条件：

CREATE TABLE Student(
    Sno CHAR(7) PRIMARY KEY,
    Sname CHAR(8) NOT NULL,
    Ssex char(2),
    Sage SMALLINT,
    Sdept CHAR(20),
    CHECK(Ssex='女' OR Sname NOT LIKE 'Mr.%'));//定义了Sname和Ssex之间的约束条件

3.4 CONSTRAINT完整性约束名子句

在定义表时利用约束命名子句对完整性约束条件命名，能够灵活地增加或删除一个完整性约束条件。

定义表Student，要求学号在[10000,19999]之间，姓名不能取空值，年龄小于30，性别只能是”男“或”女“，全部用约束命名子句实现。

CREATE TABLE Student(
    Sno NUMERIC(5)
        CONSTRAINT C1 CHECK(Sno BETWEEN 10000 AND 19999),
    Sname VARCHAR(20)
        CONSTRAINT C2 NOT NULL,
    Sage NUMERIC(3)
        CONSTRAINT C3 CHECK(Sage<30),
    Ssex VARCHAR(2)
        CONSTRAINT C4 CHECK(Ssex IN('男','女')),
    CONSTRAINT StudnetKey PRIMARY KEY(Sno));

在表Student上定义了5个约束条件，包括主码约束以及C1、C2、C3、C4四个列级约束。

修改表Student中的完整性限制，去掉对性别的限制，并将年龄的限制由小于30改为小于35

ALTER TABLE Student
    DROP CONSTRAINT C4;/*去掉对性别的限制条件C4*/
ALTER TABLE Student
    DROP CONSTRAINT C3;/*先删掉原来的限制条件再增加一个新的约束条件*/
ALTER TABLE Student
    ADD CONSTRAINT C3 CHECK(Sage<35);

3.5 触发器

触发器又叫做事件-条件-动作规则，当特定的系统时间发生时，对规则的条件进行检查，如果条件成立则执行规则中的动作，否则不能执行。
可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。

能够定义触发器的用户有：1）表的所有者；2）系统管理员；3）拥有创建触发器的权限，且拥有对操作对象的相应的操作权限的用户。

定义表Tab含一个INT型的Col属性，插入两条数据

CREATE TABLE Tab(Col INT);
INSERT INTO Tab VALUES(10);
INSERT INTO Tab VALUES(20);

再在其上定义触发器TRI，在对表Tab的插入和更新前检查，如果插入或更新的值在（100,1000）之间的话，将其置为50；如果值大于1000的话，则给出新值不允许大于1000的提示。

右键单击触发器，选择新建触发器，设置基本属性后点击确定

生成触发器成功

其对应的DDL为：

检测：

1）向表Tab中插入数据，执行INSERT INTO Tab VALUES(150);，结果：

除了在建立触发器之前插入的数据，新插入的数据150范围在(100,1000)之间，触发器TRI自动执行，插入的数据由150变成了50。

2）对表Tab中的数据进行更新，将20更新为1500，即执行UPDATE Tab SET Col=1500 WHERE Col=20;，结果：

对表进行查询后，发现数据并没有发生变化。说明在更新数据时，值大于1000时触发器TRI自动执行，系统报错，更新无效。

4、出现的问题及解决方案

将前面创建触发器生成的DDL：

CREATE
    TRIGGER TRI BEFORE INSERT
    OR UPDATE
        ON
        "PUBLIC".TAB FOR EACH ROW AS BEGIN IF NEW.Col > 100
        AND NEW.Col < 1000 THEN NEW.Col := 50;
END IF;

IF NEW.Col > 1000 THEN RAISE EXCEPTION 'New values can not more than 1000';
END IF;
END;

直接进行执行后，发生了下图所示的报错：

未解决。

但前面通过软件的步骤来操作是成功的，挺奇怪的。