《数据库系统概论》实验报告 |
题 目:实验4 数据控制(完整性部分) | 姓名 | *** | 日期 | 2009-5-20 |
实验环境:硬件:CPU:P4 硬盘:80G 内存:512M 软件:KingbaseES及其交互式查询工具ISQLW 操作系统:Windows XP |
实验内容与完成情况: 使用SQL对数据进行完整性控制(3类完整性、CHECK短语、CONSTRAIN子句、触发器)。用实验证实,当操作违反了完整性约束条件时,系统是如何处理的。因为完整性约束绝大部分是在定义表结构时进行的,因此可能需要多次定义表。如果表名发生重复的话,可以先将旧表删除后再建立。
- 实体完整性:关系模型的实体完整性在CREATE TABLE中用PRIMARY KEY定义。对单属性构成的码有两种说明方法,一种是定义为列级约束条件,另一种是定义为表级约束条件。对多个属性构成的码只有一种说明方法,即定义为表级约束条件。
[例1]定义表STUDENT,并将表中的SNO属性定义为码。 CREATE TABLE STUDENT( SNO CHAR(9) PRIMARY KEY, SNAME CHAR(20) NOT NULL, SSEX CHAR(2), SAGE SMALLINT, SDEPT CHAR(20)); 或 CREATE TABLE STUDENT( SNO CHAR(9), SNAME CHAR(20) NOT NULL, SSEX CHAR(2), SAGE SMALLINT, SDEPT CHAR(20), PRIMARY KEY(SNO)); [例2]定义表SC,将表中的SNO,CNO属性组定义为码。 CREATE TABLE SC( SNO CHAR(9) NOT NULL, CNO CHAR(4) NOT NULL, GRADE SMALLINT, PRIMARY KEY(SNO,CNO));
- 参照完整性:关系模型的参照完整性在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码,用REFERENCES短语指明这些外码参照哪些表的主码。
[例3]定义表SC中的参照完整性。 CREATE TABLE SC( SNO CHAR(9) 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));
- 用户自定义完整性:在CREATE TABLE中定义属性的同时可以根据应用要求,定义属性上的约束条件,即属性值限制,包括:列值非空(NOT NULL短语),列值唯一(UNIQUE短语),检查列值是否满足一个布尔表达式(CHECK短语)。
[例4]不允许取空值。 在定义SC表时,说明SNO,CNO,GRADE属性不允许取空值。 CREATE TABLE SC( SNO CHAR(9) NOT NULL, CNO CHAR(4) NOT NULL, GRADE SMALLINT NOT NULL, PRIMARY KEY(SNO,CNO), 。。。 ); [例5]列值唯一。 建立部门表DEPT,要求部门名称DNAME列取值唯一,部门编号DEPTNO列为主码。 CREATE TABLE DEPT( DEPTNO NUMERIC(2), DNAME CHAR(9) UNIQUE, LOCATION CHAR(10), PRIMARY KEY(DEPTNO)); [例6]用CHECK短语指定列值应该满足的条件。 定义STUDENT表的SSEX只允许取"男"或"女"。 CREATE TABLE STUDENT( SNO CHAR(9) PRIMARY KEY, SNAME CHAR(8) NOT NULL, SSEX CHAR(2) CHECK(SSEX IN('男','女')), SAGE SMALLINT, SDEPT CHAR(20)); [例7]定义SC表的GRADE的值应该在0和100之间。 CREATE TABLE SC( SNO CHAR(9) NOT NULL, CNO CHAR(4) NOT NULL, GRADE SMALLINT CHECK(GRADE>=0 AND GRADE<=100), PRIMARY KEY(SNO,CNO), FOREIGN KAY(SNO)REFERENCES STUDENT(SNO), FOREIGN KEY(CNO)REFERENCES COURSE(CNO)); [例8]元组上的约束条件:在CREATE TABLE语句中可以用CHECK短语定义元组撒谎那个的约束条件,即元组级的限制。同属性值限制相比,元组级的限制可以设置不同属性之间的取值的相互约束条件。 定义STUDENT表,当学生的性别是男时,其名字不能以"Ms."打头。 CREATE TABLE STUDENT( SNO CHAR(9), SNAME CAHR(8) NOT NULL, SSEX CHAR(2), SAGE SMALLINT, SDEPT CHAR(20), PRIMARY KEY(SNO), CHECK (SSEX='女' OR SNAME NOT LIKE 'Ms.%'));
-
CONSTRANT完整性约束命名子句:SQL在CREATE TABLE语句中提供了完整性约束命名子句CONSTRANT,用来对完整性约束条件命名,从而可以灵活地增加,删除一个完整性约束条件。
[例9]建立学生登记表STUDENT,要求学号在90000-99999之间,姓名不能取空值,年龄小于30,性别只能是"男"或"女"。 CREATE TABLE STUDENT( SNO NUMERIC(6) CONSTRANT C1 CHECK(SNO BETWEEN 90000 AND 99999), SNAME CHAR(20) CONSTRANT C2 NOT NULL, SAGE NUMERIC(3) CONSTRANT C3 CHECK(SAGE<30), SSEX CHAR(2) CONSTRANT C4 CHECK(SEX IN('男','女')), CONSTRANT STUDENTKEY PRIMARY KEY(SNO)); 在STUDENT表上建立了5个约束条件,包括主码属性(命名为STUDENTKEY)以及C1,C2,C3,C4四个列级约束。 [例10]修改表STUDENT中的约束条件,要求学号改为在900000-999999之间,年龄有小于30改为小于40. ALTER TABLE STUDENT DROP CONSTRANT C1; ALTER TABLE STUDENT ADD CONSTRANT C1 CHECK(SNO BETWEEN 900000 AND 999999), ALTER TABLE STUDENT DROP CONSTRANT C3; ALTER TABLE STUDENT ALTER CONSTRANT C3 CHECK(SAGE<40); -
触发器:触发器是由用户定义在关系表上的一类由事件驱动的特殊过程。触发器类似于约束,但是比约束更加灵活,可以实施比FOREIGN KEY约束,CHECK约束更为复杂的检查和操作,具有更精细和更强大的数据控制能力。
触发器分为三类:更新触发器,插入触发器,删除触发器。 能够定义触发器的用户有:表的所有者,系统管理员,拥有创建触发器的权限且拥有对操作对象的相应的操作权限的用户。 [例11]定义表TAB,并在其上定义触发器TRI,在对TAB的插入和更新前检查,如果插入或更新的值在100-1000之间,将值置为50;如果值大于1000的话,则给出新值不允许大于1000的提示。 CREATE TABLE TAB(COL INT); INSERT INTO TAB VALUES(10); INSERT INTO TAB VALUES(20); CREATE TRIGGER TRI BEFORE UPDATE OR INSERT ON 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; 检测:1)向表TAB插入数据(150)。 执行:INSERT INTO TAB VALUES(150); 结果:插入的数据为150,值在100-1000之间,触发器TRI自动执行,将插入的值置为50. 2)对表中的数据进行更新,将20更新为1500. 执行:UPDATE TAB SET COL=1500 WHERE COL=20; 结果:系统报出"New values can not more than 1000"的错误。 对表进行查询后发现,表中的数据并没有发生改变。说明在更新数据时,值大于1000则触发触发器TRI自动执行,系统报错,更新无效。 [例12]删除触发器TRI. DROP TROGGER TRI ON TAB; 在删除触发器TRI之后,在对表TAB进行插入和更新就不会再有以上限制了。
|
转载于:https://www.cnblogs.com/wyj553/archive/2009/05/26/1490486.html