数据完整性

数据完整性

一、实验目的

1. 掌握使用SQL语句CREATE TABLE定义约束的方法。
2. 掌握使用SQL语句ALTER TABLE增加或删除约束的方法。
3. 了解约束的各种类型。
4. 掌握使用SQL语句CREATE TRIGGER创建触发器的方法。
5. 掌握引发触发器的方法。
6. 掌握使用SQL语句DROP TRIGGER删除触发器的方法。
   二、实验内容
7. SQL语句CREATE TABLE定义约束的方法。
8. SQL语句ALTER TABLE增加或删除约束的方法。
9. SQL语句CREATE TRIGGER创建触发器的方法。
10. SQL语句DROP TRIGGER删除触发器的方法。
    三、实验步骤
11. 创建students数据库，在该数据库下创建表stu，并同时创建约束，表结构及约束要求如表1所示。
    表1 stu的表结构
    字段 类型 是否为空 约 束
    学号 char(4) 否 主键
    姓名 char(8) 是
    性别 char(2) 是
    出生日期 date 是
    CREATE DATABASE students;
    USE students;
    CREATE TABLE stu (
    学号 CHAR(4) NOT NULL PRIMARY KEY,
    姓名 CHAR(8) NULL,
    性别 CHAR(2) NULL,
    出生日期 DATE NULL
    );
    

2.创建表sc，并同时创建约束，表结构及约束要求如表2所示。
表2 sc的表结构
字段 类型 是否为空 约 束
学号 char(4) 否 外键参照stu表的学号列（约束名fk_sno)
课号 char(4) 否
成绩 decimal(5,2) 是 0≦成绩≦100
设置（学号，课号）为主键。
CREATE TABLE sc(
学号 CHAR(4) NOT NULL PRIMARY KEY,
课号 CHAR(4) NOT NULL PRIMARY KEY,
成绩 DECIMAL(5,2) NULL CHECK(0<=成绩<=100),
CONSTRAINT fk_sno FOREIGN KEY(学号) REFERENCES stu(学号,课号)
);

3. 创建表course，并同时创建约束，表结构及约束要求如表3所示。
   表3 course的表结构
   字段 类型 是否为空 约 束
   课号 char(4) 否
   课名 char(20) 是 唯一约束（约束名uq_cname)
   学分 int 是
   CREATE TABLE course(
   课号 CHAR(4) NOT NULL,
   课名 CHAR(20) NULL,
   学分 INT NULL,
   CONSTRAINT uq_cname UNIQUE(课名)
   );
   

4.在course表的课号列建立主键约束。
ALTER TABLE course
ADD PRIMARY KEY(课号)；
5.在sc表的课号列建立外键约束fk_cno，参照course表的课号列的取值，要求实现级联更新。
ALTER TABLE sc
ADD CONSTRAINT fk_cno
FOREIGN KEY(课号) REFERENCES course(课号) ON UPDATE CASCADE;
6.在stu表的姓名列建立唯一约束名uq_sname。
ALTER TABLE stu
ADD CONSTRAINT uq_sname
UNIQUE(姓名);

7.在course表的学分列建立检查约束ck_xf，检查条件为学分>0。
ALTER TABLE course
ADD CONSTRAINT ck_xf
CHECK(学分>0);
8.删除sc表的外键约束fk_cno，fk_sno。
ALTER TABLE sc
DROP CONSTRAINT fk_cno;
ALTER TABLE sc
DROP CONSTRAINT fk_sno;
9.删除stu表的主键约束。
ALTER TABLE stu
DROP PRIMARY KEY;
10.删除course表的唯一约束uq_cname。
ALTER TABLE course
DROP INDEX uq_cname;
11.创建测试表test，包含一个字段date_time，字段类型varchar(50)；
CREATE TABLE test(
date_time VARCHAR(50)
);

（1）创建触发器test_trig，实现在stu表中每插入一条学生记录后，则自动在test表中追加一条插入成功时的日期时间。SYSDATE()函数用来获取当前的日期和时间。
CREATE TRIGGER test_trig AFTER INSERT ON stu
FOR EACH ROW
INSERT INTO test VALUES(SYSDATE());

（2）为stu表插入一条记录引发触发器，查看test表的内容。
INSERT INTO stu VALUES(‘1000’,‘张三’,‘男’,‘1999-09-09’);
SELECT*FROM test;
12.在course表上创建触发器del_trig，当course表上删除一门课程时，级联删除sc表该课程的记录。删除course表的一条记录，查看sc表相应记录是否被自动删除。
CREATE TRIGGER del_trig AFTER DELETE ON course
FOR EACH ROW
DELETE FROM sc WHERE sc.课号=OLD.课号;
DELETE FROM course WHERE 课号=‘1’;
SELECT * FROM sc;

四、实验思考

1. 请说明唯一约束和主键约束之间的联系和区别。
   一张表里只能有一个主键约束，可以有多个唯一约束
   唯一性约束所在的列允许空值，但是主键约束所在的列不允许空值
   2.在course表中插入一条学分值小于0的记录，该记录能插入成功吗？
   能
   3.建立外键约束所参照的父表的列必须建立成主键吗？
   对
   4.可以建立几种类型的触发器？
   3种
   5.能否在当前数据库中为其他数据库创建触发器？
   能
2. 触发器何时被激发？
   触发器可以在有新行插入表时、更改某一行时、删除某一行时激活触发程序。
   五、实验总结
   1、收获
   通过本次实验，理解了数据库完整性约束的概念，并且通过实验验证了数据库的完整性以及触发器的使用。验证了实体完整性，参照完整性，以及用户自定义完整性并且加深了理解。更加深刻的认识到了数据库完整性的重要性。
   2、存在的问题
   对主键约束和外键约束的语句不太熟练，不太理解级联删除以及级联更新的语句，造成在实验中出了很多次错误。