当前位置：首页 >数据库 > MySQL数据库篇之完整性约束和表关系

MySQL数据库篇之完整性约束和表关系

数据库

主要内容：

　　一、完整性约束

　　二、表关系

1️⃣ 完整性约束

　　（1）何为完整性约束？　　

　　约束条件与数据类型的宽度一样，都是可选参数。

　　作用：用于保证数据的完整性和一致性

　　（2）分类主要有以下五类：

　　　　1、not null 与 default

　　　　2、unique

　　　　3、primary key

　　　　4、auto_increment

　　　　5、foreign key

PRIMARY KEY (PK)标识该字段为该表的主键，可以唯一的标识记录FOREIGN KEY (FK)标识该字段为该表的外键NOT NULL标识该字段不能为空UNIQUE KEY (UK)标识该字段的值是唯一的AUTO_INCREMENT标识该字段的值自动增长（整数类型，而且为主键）DEFAULT为该字段设置默认值UNSIGNED 无符号ZEROFILL 使用0填充

说明：

1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值sex enum('male','female') not null default 'male'age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是203. 是否是key主键 primary key外键 foreign key索引 (index,unique...)

　　（3）not null 与 default　

　　　　是否可空，null表示空，非字符串
　　　　not null - 不可空
　　　　null - 可空

　　　　默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值，如下：
　　　　create table tb1(
　　　　nid int not null defalut 2,
　　　　num int not null
　　　　)

　　　　not null的实例如下：

 ==================not null====================mysql> create table t1(id int); #id字段默认可以插入空mysql> desc t1;+-------+---------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id| int(11) | YES  | | NULL||+-------+---------+------+-----+---------+-------+mysql> insert into t1 values(); #可以插入空mysql> create table t2(id int not null); #设置字段id不为空mysql> desc t2;+-------+---------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id| int(11) | NO| | NULL||+-------+---------+------+-----+---------+-------+mysql> insert into t2 values(); #不能插入空ERROR 1364 (HY000): Field 'id' doesn't have a default value

View Code

　　　　default的实例

==================default====================#设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值mysql> create table t3(id int default 1);mysql> alter table t3 modify id int not null default 1;mysql> create table t8(-> id int,-> name char(6),-> sex enum('male','female') not null default 'male'-> );Query OK, 0 rows affected (0.41 sec)mysql> insert into t8(id,name) values(1,'cc');Query OK, 1 row affected (0.08 sec)mysql> desc t8;+-------+-----------------------+------+-----+---------+-------+| Field | Type  | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+-------+| id| int(11)| YES  | | NULL||| name  | char(6)| YES  | | NULL||| sex| enum('male','female') | NO| | male||+-------+-----------------------+------+-----+---------+-------+3 rows in set (0.01 sec)mysql> select * from t8;+------+------+------+| id| name | sex  |+------+------+------+|1 | cc| male |+------+------+------+1 row in set (0.00 sec)mysql> insert into department values-> (1,'student'),-> (2,'school');Query OK, 2 rows affected (0.06 sec)Records: 2  Duplicates: 0  Waings: 0

View Code

　　（4） unique

　　　　单列唯一

　　　　　　方法一：

 mysql> create table department(-> id int unique,-> name char(10) unique-> );Query OK, 0 rows affected (0.59 sec)

　　　　　　方法二：

  mysql> create table department(-> id int,-> name char(10),-> unique(id),-> unique(name)-> );Query OK, 0 rows affected (0.49 sec)

　　　　联合唯一（一组里的某个参数不同即可）

 mysql> create table services(-> id int unique,-> ip char(14),-> port int,-> unique(ip,port)-> );Query OK, 0 rows affected (0.48 sec)mysql> desc services;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id| int(11)  | YES  | UNI | NULL||| ip| char(14) | YES  | MUL | NULL||| port  | int(11)  | YES  | | NULL||+-------+----------+------+-----+---------+-------+3 rows in set (0.06 sec)mysql> insert into services values-> (1,'192.168.0.1',80),-> (2,'192.168.0.1',81),-> (2,'192.168.0.2',81);ERROR 1062 (23000): Duplicate entry '2' for key 'id'mysql> insert into services values-> (1,'192.168.0.1',80),-> (2,'192.168.0.1',81),-> (3,'192.168.0.2',81);Query OK, 3 rows affected (0.10 sec)Records: 3  Duplicates: 0  Waings: 0mysql> select * from services;+------+-------------+------+| id| ip  | port |+------+-------------+------+|1 | 192.168.0.1 |80 ||2 | 192.168.0.1 |81 ||3 | 192.168.0.2 |81 |+------+-------------+------+3 rows in set (0.00 sec)

View Code

　　（5）primary key --> not null unique　　　

　　primary key字段的值不为空且唯一
　　约束：not null nique
　　存储引擎（innodb）:对于innodb存储引擎来说，一张表必须有一个主键
　　一个表中可以：
　　单列做主键
　　多列做主键（复合主键）
　　但一个表内只能有一个主键primary key

　　单列主键：

 mysql> create table t9(-> id int primary key,-> name char(13)-> );Query OK, 0 rows affected (0.44 sec)mysql> desc t9;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id| int(11)  | NO| PRI | NULL||| name  | char(13) | YES  | | NULL||+-------+----------+------+-----+---------+-------+2 rows in set (0.01 sec)mysql> insert into t9 values-> (1,'cc'),-> (2,'cc2');Query OK, 2 rows affected (0.07 sec)Records: 2  Duplicates: 0  Waings: 0mysql> insert into t9 values-> (2,'cc3');ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

View Code

　　多列主键

 mysql> create table t10(-> ip char(13),-> port int,-> primary key(ip,port)-> );Query OK, 0 rows affected (0.41 sec)mysql> desc t9;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id| int(11)  | NO| PRI | NULL||| name  | char(13) | YES  | | NULL||+-------+----------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> desc t10;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| ip| char(13) | NO| PRI | ||| port  | int(11)  | NO| PRI | 0||+-------+----------+------+-----+---------+-------+2 rows in set (0.01 sec)mysql> insert into t10 values(-> '1.1.1.1',88),-> ('1.1.1.1',88);ERROR 1062 (23000): Duplicate entry '1.1.1.1-88' for key 'PRIMARY'mysql> insert into t10 values(-> '1.1.1.1',88),-> ('1.1.1.1',89);Query OK, 2 rows affected (0.06 sec)Records: 2  Duplicates: 0  Waings: 0mysql> select * from t10;+---------+------+| ip  | port |+---------+------+| 1.1.1.1 |88 || 1.1.1.1 |89 |+---------+------+2 rows in set (0.00 sec)

View Code

　　（6） auto_increment

　　约束字段为自动增长，被约束的字段必须被key约束。

 mysql> create table t11(-> id int primary key auto_increment,-> name char(12)-> );Query OK, 0 rows affected (0.56 sec)mysql> insert into t11(name) values-> ('cc1'),-> ('cc2'),-> ('cc3'),-> ('cc4');Query OK, 4 rows affected (0.08 sec)Records: 4  Duplicates: 0  Waings: 0mysql> select * from t11;+----+------+| id | name |+----+------+|  1 | cc1  ||  2 | cc2  ||  3 | cc3  ||  4 | cc4  |+----+------+4 rows in set (0.00 sec)mysql> insert into t11(name) values-> ('qq1'),-> ('qq2'),-> ('qq3');Query OK, 3 rows affected (0.09 sec)Records: 3  Duplicates: 0  Waings: 0mysql> select * from t11;+----+------+| id | name |+----+------+|  1 | cc1  ||  2 | cc2  ||  3 | cc3  ||  4 | cc4  ||  5 | qq1  ||  6 | qq2  ||  7 | qq3  |+----+------+7 rows in set (0.00 sec)了解：mysql> show variables like 'auto_inc%';+--------------------------+-------+| Variable_name| Value |+--------------------------+-------+| auto_increment_increment | 1 || auto_increment_offset| 1 |+--------------------------+-------+2 rows in set (0.00 sec)#基于会话级别set session auth_increment_increment=2 #修改会话级别的步长#基于全局级别的set global auth_increment_increment=2 #修改全局级别的步长（所有会话都生效）# 步长：auto_increment_increment默认为1设置步长mysql> set session auto_increment_increment = 5; # 零时Query OK, 0 rows affected (0.00 sec)mysql> set global auto_increment_increment=5; # 全局Query OK, 0 rows affected (0.00 sec)mysql> show variables like "auto_inc%";+--------------------------+-------+| Variable_name| Value |+--------------------------+-------+| auto_increment_increment | 5 || auto_increment_offset| 1 |+--------------------------+-------+2 rows in set (0.00 sec)#起始偏移量（起始偏移量 <= 步长）auto_increment_offset 默认1set global auto_increment_officemysql> create table t12(-> id int primary key auto_increment,-> name char(18)-> );Query OK, 0 rows affected (0.50 sec)mysql> insert into t12(name) values-> ('cc'),-> ('sc');Query OK, 2 rows affected (0.07 sec)Records: 2  Duplicates: 0  Waings: 0mysql> select * from t12;+----+------+| id | name |+----+------+|  1 | cc||  6 | sc|+----+------+2 rows in set (0.00 sec)使用极少，我们需要改回来mysql> set global auto_increment_increment=1;Query OK, 0 rows affected (0.00 sec)mysql> set global auto_increment_offset=1;Query OK, 0 rows affected (0.00 sec)清空表数据 ：truncatemysql> select * from t12;+----+------+| id | name |+----+------+|  1 | cc||  6 | sc|+----+------+2 rows in set (0.00 sec)mysql> truncate t12;Query OK, 0 rows affected (0.31 sec)mysql> select * from t12;

View Code

　　（7）foreign key（外键），建立表之间的关系

　　　　如何理解foreign key？

　　　　假如员工信息表有三个字段：工号姓名部门

　　　　公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费。该如何解决这个问题呢？

　　　　解决办法：　　　　　

　　　　　　我们可以先定义一个部门表，然后让员工信息表关联该表，如何关联，即使用 foreign key

　　　　注意：

　　　　　　表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一。

　　　　第一步，先建立被关联的表，并且保证被关联的子段唯一，此处的被关联的表是部门表（dep）如下：　　　

 mysql> create table dep(# 父表，即被关联的表-> id int primary key,-> name char(20),-> comment char(16)-> );Query OK, 0 rows affected (0.42 sec)mysql> desc dep;+---------+----------+------+-----+---------+-------+| Field| Type | Null | Key | Default | Extra |+---------+----------+------+-----+---------+-------+| id  | int(11)  | NO| PRI | NULL||| name| char(20) | YES  | | NULL||| comment | char(16) | YES  | | NULL||+---------+----------+------+-----+---------+-------+3 rows in set (0.01 sec)

　　　　第二步，建立关联的表（此处指员工表），如下：　　

  mysql> create table emp(# 子表-> id int primary key,-> name char(13),-> sex enum('male','female'),-> dep_id int,-> foreign key(dep_id) references dep(id)-> );Query OK, 0 rows affected (0.54 sec)mysql> desc emp;+--------+-----------------------+------+-----+---------+-------+| Field  | Type  | Null | Key | Default | Extra |+--------+-----------------------+------+-----+---------+-------+| id | int(11)| NO| PRI | NULL||| name| char(13)  | YES  | | NULL||| sex| enum('male','female') | YES  | | NULL||| dep_id | int(11)| YES  | MUL | NULL||+--------+-----------------------+------+-----+---------+-------+4 rows in set (0.01 sec)

　　　　第三步，先向被关联表中插入数据，再向关联表中插入数据（即先向部门表中插入数据，再向员工表插入数据）

 mysql> insert into dep values-> (1,'销售','部门1'),-> (2,'财务','部门2'),-> (3,'售后','部门3');Query OK, 3 rows affected (0.07 sec)Records: 3  Duplicates: 0  Waings: 0mysql> insert into emp values-> (1,'cc','male',1),-> (2,'sc','male',2),-> (3,'ssc','female',3),-> (4,'sscc','female',3);Query OK, 4 rows affected (0.06 sec)Records: 4  Duplicates: 0  Waings: 0mysql> select * from emp-> ;+----+------+--------+--------+| id | name | sex| dep_id |+----+------+--------+--------+|  1 | cc| male|  1 ||  2 | sc| male|  2 ||  3 | ssc  | female |  3 ||  4 | sscc | female |  3 |+----+------+--------+--------+4 rows in set (0.00 sec)mysql> select * from dep;+----+--------+---------+| id | name| comment |+----+--------+---------+|  1 | 销售| 部门1||  2 | 财务| 部门2||  3 | 售后| 部门3|+----+--------+---------+3 rows in set (0.00 sec)

　　　　第四步（删除数据才需要），先删除关联表中的数据，再删除被关联表中的数据

 mysql> delete from emp where dep_id=3;Query OK, 2 rows affected (0.06 sec)mysql> select * from emp;+----+------+------+--------+| id | name | sex  | dep_id |+----+------+------+--------+|  1 | cc| male |  1 ||  2 | sc| male |  2 |+----+------+------+--------+2 rows in set (0.00 sec)mysql> delete from dep where id=3;Query OK, 1 row affected (0.07 sec)mysql> select * from dep;+----+--------+---------+| id | name| comment |+----+--------+---------+|  1 | 销售| 部门1||  2 | 财务| 部门2|+----+--------+---------+2 rows in set (0.00 sec)

　　注意：当表中包含 FOREIGN KEY约束时，插入数据要先被关联表，后关联表；而要删除数据时，

　　顺序正好相反，先删除关联表中的数据，后删除被关联表中的数据。切记切记！！！

综合案例：　

 mysql> create table dep2(  # 被关联表-> id int primary key,-> name varchar(15) not null-> )engine=innodb;Query OK, 0 rows affected (0.36 sec)mysql> create table emp2( # 关联表-> id int primary key,-> name varchar(13) not null,-> dep2_id int ,-> foreign key(dep2_id) references dep2(id)-> on delete cascade  # 添加之后，可单独删除关联表数据-> on update cascade  # 添加之后，可单独更新关联表数据-> )engine=innodb;Query OK, 0 rows affected (0.36 sec)mysql> insert into dep2 values-> (1,'部门1'),-> (2,'部门2'),-> (3,'部门3');Query OK, 3 rows affected (0.08 sec)Records: 3  Duplicates: 0  Waings: 0mysql> insert into emp2 values-> (1,'cc1',1),-> (2,'cc2',2),-> (3,'cc2',2),-> (4,'cc2',3);Query OK, 4 rows affected (0.03 sec)Records: 4  Duplicates: 0  Waings: 0mysql> delete from emp2 where id=4;Query OK, 1 row affected (0.04 sec)mysql> delete from emp2 where id=3;Query OK, 1 row affected (0.07 sec)mysql> select * from emp2;+----+------+---------+| id | name | dep2_id |+----+------+---------+|  1 | cc1  |1 ||  2 | cc2  |2 |+----+------+---------+2 rows in set (0.00 sec)mysql> delete from dep2 where id=3;Query OK, 1 row affected (0.07 sec)mysql> select * from dep2;+----+---------+| id | name|+----+---------+|  1 | 部门1||  2 | 部门2|+----+---------+2 rows in set (0.00 sec)mysql> delete from  dep2 where id=2;Query OK, 1 row affected (0.15 sec)mysql> select * from emp2;+----+------+---------+| id | name | dep2_id |+----+------+---------+|  1 | cc1  |1 |+----+------+---------+1 row in set (0.00 sec)mysql> update emp2 set id=666 where id=1;Query OK, 1 row affected (0.09 sec)Rows matched: 1  Changed: 1  Waings: 0mysql> select * from emp2;+-----+------+---------+| id  | name | dep2_id |+-----+------+---------+| 666 | cc1  |1 |+-----+------+---------+1 row in set (0.00 sec)

View Code

　　清空表：

delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。truncate table t1; #数据量大，删除速度比上一条快，且直接从零开始，

2️⃣ 表关系

　　1、如何分析两张表间的关系？

　　实例如下，分析下面两张表：

emp+----+------+--------+--------+| id | name | sex| dep_id |+----+------+--------+--------+|  1 | cc| male|  1 |p|  2 | sc| male|  2 ||  3 | ssc  | female |  3 ||  4 | sscc | female |  3 |+----+------+--------+--------+

dep+----+--------+---------+| id | name| comment |+----+--------+---------+|  1 | 销售| 部门1||  2 | 财务| 部门2||  3 | 售后| 部门3|+----+--------+---------+

分析流程如下：

　　（1）先站在左表的角度去找

是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）

　　（2）再站在右表的角度去找

是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）

　　（3）总结：

#多对一：如果只有步骤1成立，则是左表多对一右表如果只有步骤2成立，则是右表多对一左表#多对多如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系#一对一:如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

　　2、建立表关系

　　　　2.1、多对一（一对多）

　　　　两张表：出版社、书

　　　　关联方式：foreign key

  create table press(id int primary key auto_increment,name varchar(20));create table book(id int primary key auto_increment,name varchar(20),press_id int not null,foreign key(press_id) references press(id)on delete cascadeon update cascade);insert into press(name) values('北京出版社'),('知识产权无用出版社') ;insert into book(name,press_id) values('九阳神功',1),('九阴真经',1),('葵花宝典',2);('独孤九剑',2);

View Code

　　　　　2.2、多对多

　　　　三张表：出版社，作者信息，书

　　　　多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多

　　　　如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来
　　专门存放二者的关系。

　　　　关联方式：foreign key+一张新的表（出版社信息同上，不再追加）

  create table author(id int primary key auto_increment,name varchar(20));书籍表和插入的书与之前共用create table author2book(id int not null unique auto_increment,author_id int not null,book_id int not null,(constraint fk_author) foreign key(author_id) references author(id)on delete cascade,on update cascade,(constraint fk_book) foreign key(book_id) references book(id)on delete cascadeon update cascade,primary key(author_id,book_id));insert into autor(name) values('cc1'),('cc2'),('cc3');#每个作者与自己的代表作如下cc1:九阳神功九阴真经cc2:九阳神功葵花宝典cc3:独孤九剑insert into author2book(author_id,book_id) values(1,1),(1,1),(2,1),(2,2),(3,2);

View Code

　　　2.3、一对一(一个学生是一个客户，一个客户有可能变成一个学校，即一对一的关系)

 如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可#一定是student来foreign key表customer，这样就保证了：#1 学生一定是一个客户，#2 客户不一定是学生，但有可能成为一个学生

create table customer(id int primary key auto_increment,name varchar(20) not null,phone char(16) not null);create table student(id int primary key auto_increment,class_name varchar(20) not null,customer_id int unique, #该字段一定要是唯一的foreign key(customer_id) references customer(id) #外键的字段一定要保证uniqueon delete cascadeon update cascade);# 增加客户insert into customer(name,phone)('cc',12312312),('cc2',213231231),('cc3',66666666);# 增加学生 insert into student(class_name,customer_id) values ('class1',1), ('class2',2), ('class3',3);