实验四数据控制(完整性部分).ppt
实验四 数据控制（完整性部分）,1.实体完整性,【例 1】定义表的主码。关系模型的实体完整性在 CREATE TABLE 中用 PRIMARY KEY 定义。定义主码的方法 分为定义为列级约束条件和定义为表级约束条件两种。,例 1-1定义表 Student，并将其中的 Sno 属性定义为主码。,首先建立一个模式，然后打开查询分析器 输入：CREATE TABLE STUDENT(SNO CHAR(7)PRIMARY KEY,SNAME CHAR(8)NOT NULL,SSEX CHAR(2),SAGESMALLINT,SDEPT CHAR(20);或者：CREATE TABLE STUDENT(SNOCHAR(7),SNAMECHAR(8),SSEXCHAR(2),SAGE SMALLINT,SDEPTCHAR(20),PRIMARY KEY(SNO);,模式下的就会建一个STUDENT表,例 1-2定义表 SC,将其中的属性 Sno,Cno 定义为主码。,CREATE TABLE 4.SC(SNO CHAR(7)NOT NULL,CNO CHAR(4)NOT NULL,GRADE SMALLINT,PRIMARY KEY(SNO,CNO);,2.参照完整性。,【例 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);,注意，运行前先把之前做的SC表删除,并建立COURSE表，输入下列数据,CREATE TABLE 4.COURSE(CNOCHAR(7)PRIMARY KEY,CNAME CHAR(8)NOT NULL,TNOCHAR(2)NOT NULL,CREDIT INTEGER NOT NULL);,COURSE表,SC表,3.用户定义完整性。,【例 3】用户定义的属性上的约束条件。例 3-1列值非空。在定义 SC 表时，Sno、Cno 和 Grade 属性都不允许取空值。在不特别声明的情况下，非码属性的值是允许取空值的。,一样先删除之前的SC表,CREATE TABLE“4”.”SC”(SNOCHAR(7)NOT NULL,/*列值非空的约束 NOT NULL*/CNOCHAR(4)NOT NULL,GRADESMALLINT NOT NULL);,新SC表,例 3-2列值唯一。建立部门表 DEPT，要求部门名称 Dname 取值唯一，部门编号 Deptno 属 性为主码。,CREATE TABLE 4.DEPT(DEPTNO NUMERIC(7)PRIMARY KEY,DNAME VARCHAR(9)UNIQUE,/*UNIQUE 约束要求 Dname 取值唯一*/LOCATION VARCHAR(10);,DEPT表,例 3-3CHECK 短语指定列值应该满足的条件。定义表 Student，属性 Ssex 的值只允许取“男”或“女”；先删除之前的STUDENT的表,CREATE TABLE 4.STUDENT(SNOCHAR(7)PRIMARY KEY,SNAME CHAR(8)NOT NULL,SSEXCHAR(2)CHECK(SSEX IN(男,女),/*CHECK 语句约束条件*/SAGESMALLINT,SDEPTCHAR(20);,STUDENT,定义表 SC，属性 Grade 的值定义在 0100 之间。删除之前的SC表,CREATE TABLE 4.SC(SNO CHAR(7)NOT NULL,CNO CHAR(4)NOT NULL,GRADE SMALLINT CHECK(GRADE0 AND GRADE100),/*CHECK 语句约束*/PRIMARY KEY(SNO,CNO),FOREIGN KEY(SNO)REFERENCES 4.STUDENT(SNO),FOREIGN KEY(CNO)REFERENCES 4.COURSE(CNO);,SC,【例 4】用户定义的元组上的约束条件。,CHECK 短语不光能够定义属性列上的约束条件，还允许用户定义元组级的约束条件。定义表 Student，要求当学生性别为男时，其名字不能以 Ms.打头。,STUDENT,CREATE TABLE 4.STUDENT(SNOCHAR(7)PRIMARY KEY,SNAME CHAR(8)NOT NULL,SSEXCHAR(2),SAGESMALLINT,SDEPT CHAR(20),CHECK(SSEX=女 OR SNAME NOT LIKE Ms.%);/*定义了 Sname 和 Ssex 之间的约束条件*/,4.CONSTRAINT 完整性约束命名子句。,【例 5】在定义表时利用约束命名子句对完整性约束条件命名，能够灵活地增加或删除一个 完整性约束条件。例 5-1定义表 Student，要求学号在 9000099999 之间，姓名不能取空值，年龄小于 30，性别只能是“男”或“女”。要求全部用约束命名子句实现。,STUDENT,CREATE TABLE 4.STUDENT(SNO NUMERIC(5)CONSTRAINT C1 CHECK(SNO BETWEEN 90000 AND 99999),SNAME VARCHAR(20)CONSTRAINT C2 NOT NULL,SAGE NUMERIC(3)CONSTRAINT C3 CHECK(SAGE 30),SSEX VARCHAR(2)CONSTRAINT C4 CHECK(SSEX IN(男,女),CONSTRAINT STUDENTKey PRIMARY KEY(SNO);,在表 Student 上共定义了 5 个约束条件，包括主码约束以及 C1、C2、C3、C4 四个列级 约束。,例 5-2修改表 Student 中的完整性限制，去掉对性别的限制，并将年龄的限制由小于 30 改 为小于 40。,ALTER TABLE 4.STUDENT/*去掉对性别的限制条件 C4*/DROP CONSTRAINT C4;ALTER TABLE 4.STUDENT/*先删掉原来的约束条件再增加一个新的约束条件*/DROP CONSTRAINT C3;ALTER TABLE 4.STUDENTADD CONSTRAINT C3 CHECK(SAGE 40);,5.触发器。触发器可以看成是一类特殊的存储过程，在满足某个特定条件时自动触发执行，是提高数据库服务器性能的有力工具。触发器分为三类，更新触发器、插入触发器和删除触发器。能够定义触发器的用户有：,1）表的所有者；2）系统管理员；3）拥有创建触发器的权限，且拥有对操作对象的相应的操作权限的用户。,【例 6】定义表 TAB,并在其上定义触发器 TRI，在对 TAB 的插入和更新前检查，如果插入 或更新的值在 1001000 之间的话，将值置为 50；如果值大于 1000 的话，则给出新值不允 许大于 1000 的提示。,CREATE TABLE 4.TAB(col int);INSERT INTO 4.TAB VALUES(10);INSERT INTO 4.TAB VALUES(20);CREATE TRIGGER TRI BEFORE UPDATE OR INSERTON 4.TAB FOR EACH ROW AS BEGIN IF new.col 100 AND new.col 1000 THEN RAISE EXCEPTION New values can not more than 1000;END IF;END;,TAB表,TRI触发器,检测：1）向表 TAB 插入数据（150）。执行：INSERT INTO 4.TAB VALUES(150);,结果：插入的数据为 150。值在 1001000 之间，触发器 TRI 自动执行，将插入 的值置为 50。,2）对表中的数据进行更新，将 20 更新为 1500。执行：UPDATE TAB SET COL=1500 WHERE COL=20;,结果：系统报出New values can not more than 1000的错误，对表进行查询后发现，表中的数据并没有发生改变。说明在更新数据时，值大于 1000则触发触发器 TRI 自动执行，系统报错，更新无效。,【例 7】删除触发器 TRI。DROP TRIGGER TRI ON TAB;,删除触发器 TRI 之后，在对 TAB 进行插入和更新就不再有以上限制了。,SEE YOU,
