数据库实验三：数据库的完整性和视图

一.完整性约束

(1)

departments表

右键点击departments表->设计表,给字段DepartmentID添加主
键，勾选不是null，字段DepartmentName勾选不是null

employees表

右键点击employees表->设计表，给字段DepartmentID添加主键，勾选不是null，字段Name、Birthday、Education、Sex勾选不是null,给字段Sex添加默认值1

为DepartmentID添加外键

salary表

右键点击salary表->设计表，给字段DepartmentID添加主键，勾选不是null

给DepartmentID添加外键

(2) 将员工"叶凡"编号修改为"0000001",系统提示"Data too long for column’EmployeelD’at row 1",是因为在设计表的时候EmployeeID这个字段的长度为6，“0000001"长度为7超过了规定的长度，所以报错。删除该员工的编号会报错，因为Salary表里面添加了外键，引用的字段就是"EmployeeID”

(3)将员工叶凡所在的部门编号修改为6，系统会提示"Cannot add or update a child row:a foreign key constraint fails",因为为字段EmployeeID添加了外键，对应employee表里面的EmployeeID取值只有1-5,没有值6，所以会报错。

(4)右键点击employees表->设计表，点击不是null,设置为非空，长度为1，设置默认值为’1’
例如插入一条数据不输入性别，则默认为1，性别为男，或者插入的数据Sex为‘1’或者‘0’。

如果插入的数据长度大于1，则会报错。

(5)
创建表employees_1,department_1,salary_1

CREATE TABLE Employees_1(
    EmployeeID CHAR(6) PRIMARY KEY NOT NULL UNIQUE,
    Name char(10) NOT NULL,
    Education char(4) NOT NULL,
    Birthday date NOT NULL,
    Sex char(1) default '1' NOT NULL ,
    WorkYear tinyint,
    Address varchar(40),
    PhoneNumbe char(12),
    DepartmentID char(3) NOT NULL,
		CONSTRAINT `departID` FOREIGN KEY (`DepartmentID`) REFERENCES `departments_1` (`DepartmentID`) ON DELETE RESTRICT ON UPDATE RESTRICT
		);
CREATE TABLE Departments_1(
    DepartmentID char(3) PRIMARY KEY NOT NULL UNIQUE,
    DepartmentName char(20) NOT NULL,
    Note varchar(100)
);
CREATE TABLE Salary_1(
    EmployeeID char(6) PRIMARY KEY NOT NULL UNIQUE,
    Income float,
    Outcome float,
		CONSTRAINT `employID` FOREIGN KEY (`EmployeeID`) REFERENCES `employees_1` (`DepartmentID`) ON DELETE RESTRICT ON UPDATE RESTRICT
);

二. 视图的操作：

######(1).图形化工具
点击视图->新建视图->视图创建工具->选择表department，构建并运行

点击保存，名称为DS_VIEW

sql语句

CREATE VIEW `yggl`.`DS_VIEW_1` AS SELECT
	departments.DepartmentName, 
	departments.DepartmentID, 
	departments.Note
FROM
	departments;

(2)

图形化工具

点击视图->新建视图->视图创建工具->选择表employees的PhoneNumber、Name，表salary的ActIncome，构建并运行

点击保存，名称为Employees_VIEW

sql语句

	CREATE VIEW `yggl`.`employees_VIEW_1` AS SELECT
	employees.PhoneNumber, 
	employees.`Name`, 
	salary.ActIncome
FROM
	employees,
	salary;

(3)
插入数据

SELECT
	departments.DepartmentName, 
	departments.DepartmentID, 
	departments.Note
FROM
	departments

department表中增加了一条记录

(4)
删除视图Employees_VIEW中的数据会提示‘5-Can not delete from join view 'yggl.employees_view’，视图Employees_VIEW中插入数据也会提示Can not modify more than one base table through a join view ‘yggl.employees_view’
因为视图Employees_VIEW是连接视图，数据来源于两个表，不能通过连接视图修改多个基表，所以会报错。