实验五 数据库完整性约束的实现与验证

 题目

1. 在实验四的基础上，重新创建以下三个表：
   会员表：member(memno,memname,address,telephone,username,userpwd)，主码为memno，属性memname不能取空值
   员工表：employee(empno,empname,depno,sex,telephone,username,userpwd)，主码为empno，depno参照部门表department的主码属性depn
   部门表：department(depno,depname,manager,deptotal)，主码为depno，属性depname不能取空值
2. 为employeea表的empname属性添加不能取空值的约束，并举例实践验证DBMS的违约处理机制，写出具体的实验数据、步骤和结果
3. 为department表的manager属性增加参照完整性约束，要求该属性参照员工表employee的主码属性empno，并举例实践验证DBMS的违约处理机制，写出具体的实验数据、步骤和结果
4. 为订单表orders增加主码约束，主码为orderno, 并举例实践验证DBMS的违约处理机制，写出具体的实验数据、步骤和结果
5. 为会员表member属性列username添加取唯一值的约束，约束名为UK1，删除约束，并举例实践验证增加约束前后DBMS的违约处理机制，写出具体的实验数据、步骤和结果
6. 为员工表employee的属性列username增加取唯一值的约束，并举例实践验证DBMS的违约处理机制，写出具体的实验数据、步骤和结果
7. 创建一个性别只能取“男”或“女”的规则并把其绑定到员工表employee的属性sex上，并说明使用规则定义完整性约束与直接使用create语句或alter语句定义完整性约束的区别
8. 定义一个默认值，并绑定在员工表employee的userpwd属性上，要求userpwd属性的默认取“123456”

步骤如下：

 1在实验四的基础上，重新创建以下三个表：

会员表：member(memno,memname,address,telephone,username,userpwd)，主码为memno，属性memname不能取空值

CREATE TABLE member (

    memno INT PRIMARY KEY,

    memname VARCHAR(100) NOT NULL,

    address VARCHAR(255),

    telephone VARCHAR(20),

    username VARCHAR(50),

    userpwd VARCHAR(50)

);

员工表：employee(empno,empname,depno,sex,telephone,username,userpwd)，主码为empno，depno参照部门表department的主码属性depno

.CREATE TABLE employee (

    empno INT PRIMARY KEY,

    empname VARCHAR(100),

    depno INT,

    sex CHAR(1),

    telephone VARCHAR(20),

    username VARCHAR(50),

    userpwd VARCHAR(50),

    FOREIGN KEY (depno) REFERENCES department(depno)

);

部门表：department(depno,depname,manager,deptotal)，主码为depno，属性depname不能取空值

 CREATE TABLE department (

    depno INT PRIMARY KEY,

    depname VARCHAR(100) NOT NULL,

    manager VARCHAR(100),

    deptotal INT

);

2. 为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 失败。

语句已终止。

1. 为department表的manager属性增加参照完整性约束，要求该属性参照员工表employee的主码属性empno，并举例实践验证DBMS的违约处理机制，写出具体的实验数据、步骤和结果

首先在employee中插入数据

INSERT INTO employee (empno, empname, depno, sex, telephone, username, userpwd) VALUES

(1, 'John Doe', 101, 'M', '1234567890', 'johndoe', 'password123'),

(2, 'Jane Smith', 102, 'F', '0987654321', 'janesmith', 'password456');

添加约束条件

ALTER TABLE department

ADD CONSTRAINT fk_manager

FOREIGN KEY (manager) REFERENCES employee(empno);

再次插入empno中不存在的数据

INSERT INTO department (depno, depname, manager, deptotal) VALUES

(202, 'Finance', 3, 20);

1. 为订单表orders增加主码约束，主码为orderno, 并举例实践验证DBMS的违约处理机制，写出具体的实验数据、步骤和结果

注：orders有member和product的外键

首先在member中插入数据

insert into member values(1,'zhangsan','2156','165','zhangsan','13546');

再在product中插入数据

INSERT INTO product (prono, proname, brand, stock, supno, price, cost, catno, ontime, status)

VALUES (1, '华为', '华为', 20, 14001, 4988, 4488, 101, '2021-01-01', 0);

然后插入数据

insert into orders values(1,1,1,1,1.1,1.1,'2000-01-01','2020-02-02')。

设置主键

ALTER TABLE orders

ADD CONSTRAINT pk PRIMARY KEY (orderno);

再次添加数据

insert into orders values(1,1,1,1,1.1,1.1,'2000-01-01','2020-02-02')。

1. 为会员表member属性列username添加取唯一值的约束，约束名为UK1，删除约束，并举例实践验证增加约束前后DBMS的违约处理机制，写出具体的实验数据、步骤和结果

首先插入一组数据，此数据zhangsan是没有加的之前

INSERT INTO member  VALUES (2,'zhangsan','饭都花园','032', 'zhangsan','3516');

查看数据中是否含有重复的username导致约束无法成立。

SELECT * FROM member;

加入约束

alter table member 

add constraint UK1 unique(username) 

再次插入上述相同数据
INSERT INTO member  VALUES (2,'zhangsan','饭都花园','032', 'zhangsan','3516');

删除约束

alter table member

drop constraint UK1

再次插入数据INSERT INTO member  VALUES (2,'zhangsan','饭都花园','032', 'zhangsan','3516');

6.为员工表employee的属性列username增加取唯一值的约束，并举例实践验证DBMS的违约处理机制，写出具体的实验数据、步骤和结果

先检查数据库中是否存在重复数据。

select*from employee

没有后插入相关约束

alter table employee

add unique(username)

insert into employee(empno,depno,username)values(4,1,'zhangsi')

7创建一个性别只能取“男”或“女”的规则并把其绑定到员工表employee的属性sex上

添加约束条件check

ALTER TABLE employee

ADD constraint chek_sex check(sex in('男','女'))

将数据进行插入

insert into employee values(5,'zhangsan',101,'男','56165','zhangsi','123456')

将不符合规则的数据插入

8定义一个默认值，并绑定在员工表employee的userpwd属性上，要求userpwd属性的默认取“123456”

添加默认语句

ALTER TABLE employee

ALTER COLUMN userpwd  DEFAULT '123456';

insert into employee values(7,'lisi',1,'男','123','lisi',)