数据库原理B实验报告完整版4-6

实验四完整性日期 2011-4-6一实验目的1. 熟悉通过SQL对数据进行完整性控制。

2. 深入理解数据库系统的完整性概念。

二、实验原理为维护数据库的完整性，DBMS必须：1.提供定义完整性约束条件的机制2.提供完整性检查的方法3.违约处理Create table 语法中包含了对完整性的定义。

CREATE TABLE[ database_name.[ owner ] .| owner.] table_name( { < column_definition >| column_name AS computed_column_expression| < table_constraint > ::= [ CONSTRAINT constraint_name ] } | [ { PRIMARY KEY | UNIQUE } [ ,...n ])[ ON { filegroup | DEFAULT } ][ TEXTIMAGE_ON { filegroup | DEFAULT } ]< column_definition > ::= { column_name data_type }[ COLLATE < collation_name > ][ [ DEFAULT constant_expression ]| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ] ][ ROWGUIDCOL][ < column_constraint > ] [ ...n ]< column_constraint > ::= [ CONSTRAINT constraint_name ]{ [ NULL | NOT NULL ]| [ { PRIMARY KEY | UNIQUE }[ CLUSTERED | NONCLUSTERED ][ WITH FILLFACTOR = fillfactor ][ON {filegroup | DEFAULT} ] ]]| [ [ FOREIGN KEY ]REFERENCES ref_table [ ( ref_column ) ][ ON DELETE { CASCADE | NO ACTION } ][ ON UPDATE { CASCADE | NO ACTION } ][ NOT FOR REPLICATION ]]| CHECK [ NOT FOR REPLICATION ]( logical_expression )}< table_constraint > ::= [ CONSTRAINT constraint_name ]{ [ { PRIMARY KEY | UNIQUE }[ CLUSTERED | NONCLUSTERED ]{ ( column [ ASC | DESC ] [ ,...n ] ) }[ WITH FILLFACTOR = fillfactor ][ ON { filegroup | DEFAULT } ]]| FOREIGN KEY[ ( column [ ,...n ] ) ]REFERENCES ref_table [ ( ref_column [ ,...n ] ) ][ ON DELETE { CASCADE | NO ACTION } ][ ON UPDATE { CASCADE | NO ACTION } ][ NOT FOR REPLICATION ]| CHECK [ NOT FOR REPLICATION ]( search_conditions )}三、实验仪器和设备1. 计算机2. SQL Server 2000四、预习要求复习完整性相关的SQL语句的基本语法。

五、实验内容及步骤1. 实体完整性定义表的主码关系模型的实体完整性在 CREATE TABLE 中用 PRIMARY KEY 定义。

定义主码的方法分为定义为列级约束条件和定义为表级约束条件两种。

[例 4-1]定义表 Student，并将其中的 Sno 属性定义为主码。

CREATE TABLE STUDENT(SNOCHAR(7) PRIMARY KEY,SNAME CHAR(8) NOT NULL,SSEX CHAR(2),SAGE SMALLINT,SDEPT CHAR(20));[例 4-2]定义表 SC,将其中的属性 Sno,Cno 定义为主码。

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

CREATE TABLE SC(CNO CHAR(4) NOT NULL,GRADE SMALLINT,PRIMARY KEY (SNO,CNO));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. 用户定义完整性用户定义的属性上的约束条件[例 6-1]列值非空。

在定义 SC 表时，Sno、Cno 和 Grade 属性都不允许取空值。

在不特别声明的情况下，非码属性的值是允许取空值的。

CREATE TABLE SC(SNO CHAR(7) NOT NULL, /*列值非空的约束 NOT NULL*/CNO CHAR(4) NOT NULL,GRADE SMALLINT NOT NULL);[例 6-2]列值唯一。

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

CREATE TABLE DEPT(DEPTNO NUMERIC(7) PRIMARY KEY,DNAME VARCHAR(9) UNIQUE, /*UNIQUE 约束要求 Dname 取值唯一*/LOCATION VARCHAR(10));[例 6-3]CHECK 短语指定列值应该满足的条件。

定义表 Student，属性Ssex 的值只允许取“男”或“女”；定义表 SC，属性 Grade 的值定义在 0－100 之间。

CREATE TABLE STUDENT(SNOCHAR(7) PRIMARY KEY,SNAME CHAR(8) NOT NULL,SSEX CHAR(2) CHECK(SSEX IN(‘男’,’女’)), /*CHECK 语句约束条件*/SAGE SMALLINT,SDEPT CHAR(20));CREATE TABLE SC(SNO CHAR(7) NOT NULL,GRADE SMALLINT CHECK (GRADE>0 AND GRADE<100), /*CHECK语句约束条件*/PRIMARY KEY (SNO,CNO),FOREIGN KEY (SNO) REFERENCES STUDENT(SNO),FOREIGN KEY (CNO) REFERENCES COURSE(CNO));用户定义的元组上的约束条件CHECK 短语不光能够定义属性列上的约束条件，还允许用户定义元组级的约束条件。

定义表 Student，要求当学生性别为男时，其名字不能以 Ms.打头。

CREATE TABLE STUDENT(SNOCHAR(7) PRIMARY KEY,SNAME CHAR(8) NOT NULL,SSEX CHAR(2),SAGE SMALLINT,SDEPT CHAR(20),CHECK (SSEX = ‘女’ OR SNAME NOT LIKE ‘Ms.%’));/*定义了 Sname 和 Ssex 之间的约束条件*/4. CONSTRAINT 完整性约束命名子句。

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

[例 8-1]定义表 Student，要求学号在 90000－99999 之间，姓名不能取空值，年龄小于 30，性别只能是“男”或“女”。

要求全部用约束命名子句实现。

CREATE TABLE 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 四个列级约束。

[例 8-2]修改表 Student 中的完整性限制，去掉对性别的限制，并将年龄的限制由小于 30 改为小于 40。

ALTER TABLE STUDENT /*去掉对性别的限制条件 C4*/DROP CONSTRAINT C4;ALTER TABLE STUDENT /*先删掉原来的约束条件再增加一个新的约束条件*/DROP CONSTRAINT C3;ALTER TABLE STUDENTADD CONSTRAINT C3 CHECK(SAGE <40);5. 触发器。

触发器可以看成是一类特殊的存储过程，在满足某个特定条件时自动触发执行，是提高数据库服务器性能的有力工具。

触发器分为三类，更新触发器、插入触发器和删除触发器。

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

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