第二章数据库和表的设计
文章目录
数据完整性约束是在表和字段上强制执行的数据检验规则,为了防止不规范的数据进入数据库,在用户对数据进行插入、修改、删除等操作时,DBMS自动按照一定的约束条件对数据进行监测,主要是对空值和重复值的约束,使不符合规范的数据不能进入数据库,以保证数据存储的完整性和准确性
约束类型: | 主键 | 外键 | 唯一 | 非空 | 自增 | 默认值 |
---|---|---|---|---|---|---|
关键字: | primary key | foreign key | unique | not null | auto_increment | default |
完整性分类
按照约束的不同类型可以分为实体完整性(primary key、unique、auto_increment)、域完整性(not null、default)、参照完整性(foreign key)、用户自定义完整性(check)。
实体完整性
用来标识表中的每一条记录,一条记录代表一个实体(entity)。
主键约束(primary key)
- 每个表中只能有一个主键。
- 主键值须非空不重复。
- 可设置单字段主键,也可设置多字段联合主键。
- 联合主键中多个字段的数据完全相同时,才违反主键约束。
列级添加主键约束:
creat table <表名> (
<字段名1> <字段类型1> primary key,
<字段名2> <字段类型2>,
……
<字段名n> <字段类型n>);
示例:
mysql>CREATE TABLE classif(
id INT(11) PRIMARY KEY,-- 班级id主键
className VARCHAR(20),-- 班级名称
gradeId INT(11),-- 所属年级id
BeginYear year,-- 开班年份);
表级添加主键约束:
create table <表名>(
<字段名1> <字段类型1>,
<字段名2> <字段类型2>,
......
<字段名n> <字段类型n>,
[constraint 主键约束名] primary key(字段名1[,字段名2,...字段名n]));
示例:
mysql>CREATE TABLE classif(
id INT(11),-- 班级id主键
className VARCHAR(20),-- 班级名称
gradeId INT(11),-- 所属年级id
BeginYear year,-- 开班年份
PRIMARY KEY(id)-- 设置id为主键
);
已有表添加主键约束
alter table <表名> add [constraint 主键约束名] primary key(字段名);
示例:
-- 创建无主键约束的表
mysql> create table employee(
-> e_id int,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int,
-> d_id int);
-- 添加主键约束
mysql> alter table employee add primary key(e_id);
删除主键约束:
alter table <表名> drop primary key;
示例:
alter table employee drop primary key;
唯一约束(unique)
- 指定列的数据不能重复。
- 可以为空,但只能出现一个空值。
列级添加唯一约束:
creat table <表名> (
<字段名1> <字段类型1> unique,
<字段名2> <字段类型2>,
……
<字段名n> <字段类型n>);
示例:
mysql> create table employee(
-> e_id int,
-> e_name varchar(5) unique,
-> e_sex varchar(5),
-> e_age int,
-> d_id int);
表级添加唯一约束:
creat table <表名> (
<字段名1> <字段类型1>,
<字段名2> <字段类型2>,
……
<字段名n> <字段类型n>,
[constraint 唯一约束名] unique (字段名1[,字段名2...字段名n]));
示例:
mysql> create table employee(
-> e_id int,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int,
-> d_id int,
-> unique (e_name,e_sex));
已有表添加唯一约束:
alter table <表名> add [constraint 唯一约束名] unique (字段名);
示例:
-- 创建表
mysql> create table employee(
-> e_id int,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int,
-> d_id int);
-- 添加唯一约束
mysql> alter table employee add unique(e_name);
删除唯一约束:
alter table <表名> drop index <唯一约束名>;
如果单个字段没有指定唯一约束名,则默认的唯一约束名为字段名。如果是多个字段组合为唯一约束时候,默认的唯一约束名为第一个字段的名称。如果指定了约束名,则删除的时候写约束名。
示例:
alter table employee drop index e_name;
自动增长列(auto_increment)
- 指定字段的数据自动增长。
- 配合主键一起使用,并且只适用于整数类型。
- 默认从1开始,每增加一条记录,该字段的值会增加1。
- 即使数据删除,还是从删除的序号继续增长。
创建自动增长约束:
creat table <表名> (
<字段名1> <字段类型1> primary key auto_increment,
<字段名2> <字段类型2>,
……
<字段名n> <字段类型n>);
示例:
mysql> CREATE TABLE student_add(
id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,-- 主键id
contact VARCHAR(15) NOT NULL, -- 联系人姓名
addressDesc VARCHAR(50) NOT NULL, -- 收货地址明细
postCodde VARCHAR(15) NOT NULL, -- 邮编
tel VARCHAR(20) NOT NULL, -- 联系人电话
createdBy BIGINT(20) NOT NULL, -- 创造者
creationDate datetime, -- 创建时间
modifyBy BIGINT(20),-- 修改者
modifyDate datetime,-- 修改时间
userld BIGINT(20) -- 用户id
);
已有表添加自动增长约束:
alter table <表名> modify <字段名> <字段类型> auto_increment;
示例:
-- 创建无自增约束的表
mysql> create table employee(
-> e_id int primary key,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int,
-> d_id int);
-- 添加自增约束
alter table employee modify e_id int auto_increment;
设置自动增长初始值:
alter table <表名> auto_increment=初始值;
示例:
alter table employee auto_increment=1000;
删除自增约束:
alter table <表名> modify <字段名> <字段类型>;
示例:
alter table employee modify e_id int;
域完整性
域完整性是针对单元格的约束,不与其它行参考比较。
非空约束(not null)
- 字段的值不能为空。
创建非空约束:
creat table <表名> (
<字段名1> <字段类型1> not null,
<字段名2> <字段类型2>,
……
<字段名n> <字段类型n>);
示例:
mysql> create table employee(
-> e_id int,
-> e_name varchar(5) not null,
-> e_sex varchar(5),
-> e_age int,
-> d_id int);
已有表添加非空约束:
alter table <表名> modify <字段名> <字段类型> not null;
示例:
-- 创建表
mysql> create table employee(
-> e_id int,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int,
-> d_id int);
-- 添加非空约束
alter table employee modify e_name int not null;
删除非空约束:
alter table <表名> modify <字段名> <字段类型> [null];
示例:
alter table employee modify e_name int;
默认约束(default)
- 如果新插入一条记录时没有为该字段赋值,系统会自动为这个字段赋值为默认约束设定的值。
- 如果插入的数据为“null”,则不会使用默认值,只有没有插入数据时候,才会使用默认值。
创建默认约束:
creat table <表名> (
<字段名1> <字段类型1> default value,
<字段名2> <字段类型2>,
……
<字段名n> <字段类型n>);
示例:
mysql> create table employee(
-> e_id int,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int default 0,
-> d_id int);
已有表添加默认约束:
alter table <表名> modify <字段名> <字段类型> default value;
示例:
-- 创建表
mysql> create table employee(
-> e_id int,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int,
-> d_id int);
-- 添加默认约束
alter table employee modify e_age int default 0;
删除默认约束:
alter table <表名> modify <字段名> <字段类型>;
示例:
alter table employee modify e_age int;
参照完整性
参照完整性指多表之间的对应关系,在一张表中执行数据插入、更新、删除等操作时,DBMS都会跟另一张表进行对照,避免不规范的操作,以确保数据存储的完整性。
外键约束(foreign key)
1.某一表中某字段的值依赖于另一张表中某字段的值
2.主键所在的表为主表,外键所在的表为从表
3.每一个外键值必须与另一个表中的主键值相对应
创建外键约束:
creat table <表名> (
<字段名1> <字段类型1>,
<字段名2> <字段类型2>,
……
<字段名n> <字段类型n>,
[constraint 外键约束名] foreign key(字段名) references <主表>(主键字段));
示例:
-- 班级表
-- 创建一个主表
CREATE TABLE Classinfo(
id INT(11),-- 班级编号
ClassName VARCHAR(20),-- 班级名称
Gradeid INT(11),-- 所属年级编号
BeginYear YEAR ,-- 开班年份
PRIMARY key(id)-- 设置id为主键
);
-- 学生表
-- 创建从表的同时添加外键
CREATE TABLE StudentInfo (
ID INT (10) PRIMARY KEY,
studentName VARCHAR (20) NOT NULL,
gender VARCHAR(2) DEFAULT'男',
age int(5),
birthday DATE NOT null,
studentNo VARCHAR(12) not null UNIQUE,
calssid int ,
begtime date not null,
phone VARCHAR(20) not null,
province VARCHAR(10) DEFAULT'河南',
city VARCHAR(10),
email VARCHAR(10) not null,
-- 添加外键
FOREIGN KEY(calssid) REFERENCES classinfo(ID)
);
已有表中添加外键约束:
alter table <表名> add [constraint 外键约束名] foreign key(字段名) references <主表>(主键字段);
示例:
-- 创建一个主表
mysql> create table department(
-> d_id int primary key,
-> d_name varchar(5),
-> d_num int);
-- 创建从表
mysql> create table employee(
-> e_id int primary key,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int,
-> d_id int);
-- 添加外键约束
mysql> alter table employee add constraint fk_d_id foreign key(d_id) references department(d_id));
在有外键约束的表中导入数据时,如果数据不完整就会触发外键约束,这种情况下可以使用FOREIGN_KEY_CHECKS设置是否检查外键约束。
set FOREIGN_KEY_CHECKS=0; #在导入前设置为不检查外键约束
set FOREIGN_KEY_CHECKS=1; #在导入后恢复检查外键约束
删除外键约束
alter table <表名> drop foreign key <外键约束名>;
示例:
alter table employee drop foreign key fk_d_id;
1. 先删除从表再删除主表。
2. 先删除外键约束,再删除表。
用户自定义完整性
根据用户实际的完整性要求来定义,在执行数据插入、更新等操作时,DBMS会检查数据是否满足检查约束中的限定条件,避免不符合条件的操作,以保证数据存储的准确性。
检查约束(check)
指定需要检查的限定条件。
创建检查约束:
creat table <表名> (
<字段名1> <字段类型1>,
<字段名2> <字段类型2>,
……
<字段名n> <字段类型n>,
check(<限定条件>));
示例:
mysql> create table employee(
-> e_id int primary key,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int,
-> d_id int,
-> check(e_age>=0));
已有表添加检查约束:
alter table <表名> add constraint <检查约束名> check(<限定条件>);
示例:
-- 创建表
mysql> create table employee(
-> e_id int primary key,
-> e_name varchar(5),
-> e_sex varchar(5),
-> e_age int,
-> d_id int);
-- 添加检查约束
mysql> alter table employee add constraint ch_e_age check(e_age>=0));
删除检查约束:
alter table <表名> drop constraint <检查约束名>;
示例:
mysql> alter table employee drop constraint ch_e_age;