【数据库原理】实验报告-实验5数据库完整性约束的实现与验证

实验名称	实验五 数据库完整性约束的实现与验证	实验室	206
实验 目的 或 要求	掌握SQL定义实体完整性、参照完整性、用户自定义完整性的方法 加深理解并通过实践操作验证RDBMS针对实体完整性、参照完整性、用户自定义完整性的违约处理机制。 掌握规则和默认值的SQL定义语句
实验 环境	SQL Server 2014
实验内容或 实验题目	在实验四的基础上，重新创建以下三个表： 会员表：member(memno,memname,address,telephone,username,userpwd)，主码为memno，属性memname不能取空值 员工表：employee(empno,empname,depno,sex,telephone,username,userpwd)，主码为empno，depno参照部门表department的主码属性depno 部门表：department(depno,depname,manager,deptotal)，主码为depno，属性depname不能取空值 为employeea表的empname属性添加不能取空值的约束，并举例实践验证DBMS的违约处理机制，写出具体的实验数据、步骤和结果 为department表的manager属性增加参照完整性约束，要求该属性参照员工表employee的主码属性empno，并举例实践验证DBMS的违约处理机制，写出具体的实验数据、步骤和结果 为订单表orders增加主码约束，主码为orderno, 并举例实践验证DBMS的违约处理机制，写出具体的实验数据、步骤和结果 为会员表member属性列username添加取唯一值的约束，约束名为UK1，删除约束，并举例实践验证增加约束前后DBMS的违约处理机制，写出具体的实验数据、步骤和结果 为员工表employee的属性列username增加取唯一值的约束，并举例实践验证DBMS的违约处理机制，写出具体的实验数据、步骤和结果 创建一个性别只能取“男”或“女”的规则并把其绑定到员工表employee的属性sex上，并说明使用规则定义完整性约束与直接使用create语句或alter语句定义完整性约束的区别 定义一个默认值，并绑定在员工表employee的userpwd属性上，要求userpwd属性的默认取“123456”
实 验 原 理 （ 操 作 步 骤 ）	步骤如下：  1.在实验四的基础上，重新创建以下三个表： 会员表：member(memno,memname,address,telephone,username,userpwd)，主码为memno，属性memname不能取空值 员工表：employee(empno,empname,depno,sex,telephone,username,userpwd)，主码为empno，depno参照部门表department的主码属性depno 部门表：department(depno,depname,manager,deptotal)，主码为depno，属性depname不能取空值 首先，创建member表和department表，接下来创建employee表： -- 创建会员表 CREATE TABLE member (     memno INT PRIMARY KEY,     memname VARCHAR(50) NOT NULL,     address VARCHAR(100),     telephone VARCHAR(20),     username VARCHAR(50),     userpwd VARCHAR(50) ); -- 创建部门表 CREATE TABLE department (     depno INT PRIMARY KEY,     depname VARCHAR(50) NOT NULL,     manager INT,     deptotal INT ); -- 创建员工表 CREATE TABLE employee (     empno INT PRIMARY KEY,     empname VARCHAR(50) NOT NULL,     depno INT,     sex VARCHAR(10),     telephone VARCHAR(20),     username VARCHAR(50),     userpwd VARCHAR(50),     FOREIGN KEY (depno) REFERENCES department(depno) ); 为employeea表的empname属性添加不能取空值的约束，并举例实践验证DBMS的违约处理机制，写出具体的实验数据、步骤和结果 首先执行如下sql命令，添加约束 alter table employee  add constraint CK_employee_empname check (empname IS NOT NULL);  验证违约处理机制: 尝试插入如下数据： INSERT INTO employee (empname, depno, sex, telephone, username, userpwd)  VALUES (NULL, 1, '男', '15510810888', 'zhangsan', '123456'); 返回结果： 消息515，级别16，状态2，第1 行 不能将值NULL 插入列'empname'，表'ecommerce.dbo.employee'；列不允许有Null 值。INSERT 失败。 语句已终止。  为department表的manager属性增加参照完整性约束，要求该属性参照员工表employee的主码属性empno，并举例实践验证DBMS的违约处理机制，写出具体的实验数据、步骤和结果 输入命令： alter table department  add constraint FK_department_manager  foreign key (manager) references employee(empno); 查询结果，已成功添加外检 尝试插入错误语句 INSERT INTO department (depno, depname, manager, deptotal) VALUES ('1211', '财务部', 99, 10); 返回结果： 消息547，级别16，状态0，第1 行 INSERT 语句与FOREIGN KEY 约束"FK_department_manager"冲突。该冲突发生于数据库"ecommerce"，表"dbo.employee", column 'empno'。 语句已终止。 4为订单表orders增加主码约束，主码为orderno, 并举例实践验证DBMS的违约处理机制，写出具体的实验数据、步骤和结果 输入命令： ALTER TABLE orders  ADD CONSTRAINT PK_orders PRIMARY KEY (orderno); 返回结果 消息8111，级别16，状态1，第1 行 无法在表'orders' 中可为Null 的列上定义PRIMARY KEY 约束。 消息1750，级别16，状态0，第1 行 无法创建约束。请参阅前面的错误消息。 可见主键不能为空，因此我们执行如下命令，设置主键不为空： alter table orders  add constraint CK_orders_orderno check (orderno IS NOT NULL); 接下来再次执行命令： ALTER TABLE orders  ADD CONSTRAINT PK_orders PRIMARY KEY (orderno); 提示执行成功，查看结果 验证违约处理： 尝试插入两条具有相同orderno的记录 第一次插入insert into orders(orderno)values('88')提示成功 第二次插入提示：消息2627，级别14，状态1，第1 行 违反了PRIMARY KEY 约束'PK_orders'。不能在对象'dbo.orders' 中插入重复键。 \ 结果： 第一步成功执行，orders表成功设置主键 第二步插入重复主键值时，因主键唯一性约束而拒绝第二条记录的插入。 5为会员表member属性列username添加取唯一值的约束，约束名为UK1，删除约束，并举例实践验证增加约束前后DBMS的违约处理机制，写出具体的实验数据、步骤和结果 创建约束命令： ALTER TABLE member ADD CONSTRAINT UK1 UNIQUE (username); 删除约束命令： ALTER TABLE member DROP CONSTRAINT UK1; 验证违约处理： 尝试插入两条具有相同的记录 第一次插入 insert into member(username,memno,memname)values('111111111','1','张三')成功 第二次插入提示：消息2627，级别14，状态1，第1 行 违反了PRIMARY KEY 约束'PK__member__338C9A36060DEAE8'。不能在对象'dbo.member' 中插入重复键。 语句已终止。 删除约束后，再次插入提示成功 6为员工表employee的属性列username增加取唯一值的约束，并举例实践验证DBMS的违约处理机制，写出具体的实验数据、步骤和结果 输入SQL命令： 创建约束命令： ALTER TABLE employee ADD CONSTRAINT UK_employee_username UNIQUE (username); 验证违约处理： 尝试插入两条具有相同的记录 第一次插入 insert into employee(empno,empname,username) values('111','1111','11111')成功 第二次插入提示：消息2627，级别14，状态1，第1 行 违反了PRIMARY KEY 约束'PK__employee__AF4C318A1367E606'。不能在对象'dbo.employee' 中插入重复键。 语句已终止。 7创建一个性别只能取“男”或“女”的规则并把其绑定到员工表employee的属性sex上，并说明使用规则定义完整性约束与直接使用create语句或alter语句定义完整性约束的区别 输入命令： CREATE RULE Rule_Sex_Check AS @Sex IN ('男', '女'); EXEC sp_bindrule 'Rule_Sex_Check', 'employee.sex'; 使用alter语句： ALTER TABLE employee  ADD CONSTRAINT CHK_employee_sex CHECK (sex IN ('男', '女')); 区别： ①使用规则定义完整性约束可将约束逻辑集中管理，更易于维护。 ②直接使用CREATE语句或ALTER语句定义的完整性约束更为直接，但可能导致约束逻辑分散在不同的地方，不易于管理。 8定义一个默认值，并绑定在员工表employee的userpwd属性上，要求userpwd属性的默认取“123456”  输入sql命令： ALTER TABLE employee ADD CONSTRAINT DF_employee_userpwd DEFAULT '123456' FOR userpwd;

组内 分工 （ 可选 ）
实 验 结 果 分 析 及 心 得 体 会	分析结果以及在试验中应注意的问题: 1.在对数据库结构进行更改之前，先进行备份，防止意外情况发生 2.数据插入测试： 在验证约束的违约处理时，应该尝试插入重复值、空值或不符合约束条件的数据，以确保约束起到预期的作用。