MYSQL数据完整性约束
目的:
1、掌握数据完整性的概念和分类。
2、掌握主键、候选键、自增、外键、默认值和CHECK约束的概念和创建方法。
实验环境
MySQL8.0.
实现任务
提升训练:在前章节的图书馆管理的数据库dblibrary中,完成创建表的操作。
(1)在前章节的图书馆管理的数据库dblibrary中,完成下述创建表的操作,要求如下:
表3.6 图书表Book结构
字段名称 | 字段内容 | 数据类型 | 长度 | 说明 |
Bookid | 图书编号 | char | 20 | 非空、主键 |
Booktitle | 图书名称 | varchar | 40 | 非空 |
ISBN | ISBN号 | char | 21 | |
Typeid | 图书类别 | tinyint | 外键引用图书分类表的类别号 | |
Author | 作者 | varchar | 30 | |
Press | 出版社 | varchar | 30 | |
Pubdate | 出版日期 | date | ||
Price | 价格 | decimal | 10,2 | |
Regdate | 入库日期 | date | ||
State | 当前状态 | varchar | 10 |
mysql> create table book(
-> bookid char(20) key not null,
-> booktitle varchar(40) not null,
-> isbn char(21),
-> typeid tinyint,
-> author varchar(30),
-> press varchar(30),
-> pubdate date,
-> price decimal(10,2),
-> regdate date,
-> state varchar(10),
-> FOREIGN KEY(typeid) REFERENCES booktype(typeid));
(3)创建图书分类表Booktype,表结构如下表3.7所示。
表3.7 图书分类表Booktype结构
字段名称 | 字段内容 | 数据类型 | 长度 | 说明 |
Typeid | 类别号 | tinyint | 非空、主键、自增列 | |
Typename | 类别名称 | varchar | 20 | 非空 |
mysql> create table booktype(
-> typeid tinyint key AUTO_INCREMENT not null,
-> typename varchar(20) not null);
(4)创建读者表Reader,表结构如下表3.8所示。
表3.8 读者表Reader结构
字段名称 | 字段内容 | 数据类型 | 长度 | 说明 |
Readerid | 读者号 | char | 13 | 非空、主键 |
Readername | 姓名 | varchar | 20 | 非空 |
Typeid | 类别号 | tinyint | 外键引用读者分类表的类别号 | |
Birthday | 生日 | date | ||
Sex | 性别 | char | 1 | 非空、默认值为“男”,取值为“男”或“女” |
Tel | 电话 | char | 11 | |
Enrolldate | 注册日期 | date | 非空 | |
State | 当前状态 | char | 10 | 取值为“有效”或“无效” |
mysql> CREATE TABLE IF NOT EXISTS reader(
-> readerid CHAR(13) KEY NOT NULL,
-> readername VARCHAR(20) NOT NULL,
-> typeid TINYINT,
-> birthday DATE,
-> sex CHAR(1) NOT NULL CHECK(Sex=`男` OR Sex=`女`),
-> tel CHAR(11),
-> enrolldate DATE NOT NULL,
-> state CHAR(10) CHECK(Sex=`有效` OR Sex=`无效`),
-> FOREIGN KEY(typeid) REFERENCES readertype(typeid));
(5)创建读者分类表Readertype,表结构如下表3.9所示。
表3.9 读者分类表Readertype结构
字段名称 | 字段内容 | 数据类型 | 长度 | 说明 |
Typeid | 类别号 | tinyint | 非空、主键、自增列 | |
Typename | 类别名称 | varchar | 20 | 非空 |
Booksum | 借书最大数量 | tinyint | 非空 | |
Bookday | 借书期限 | smallint | 非空 |
mysql> create table if not exists readertype(
-> typeid tinyint key AUTO_INCREMENT not null,
-> typename varchar(20) not null,
-> booksum tinyint not null,
-> bookday smallint not null);
(6)创建借阅记录表Record,表结构如下表3.10所示。
表3.10 借阅记录表Record结构
字段名称 | 字段内容 | 数据类型 | 长度 | 说明 |
Recordid | 记录编号 | Int | 非空、主键 | |
Readerid | 读者编号 | char | 13 | 非空、外键引用读者分类表的类别号 |
Bookid | 图书编号 | char | 20 | 非空、外键引用图书表的类别号 |
Outdate | 借出日期 | date | 非空 | |
Indate | 还入日期 | date | ||
State | 状态 | varchar | 10 | 非空 |
mysql> create table if not exists record(
-> recordid int key not null,
-> readerid char(13) not null,
-> bookid char(20) not null,
-> outdate date not null,
-> indate date,
-> state varchar(10) not null,
-> FOREIGN KEY(readerid) REFERENCES reader(readerid),
-> FOREIGN KEY(bookid) REFERENCES book(bookid));