mysql表的完整性约束
概览
为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，
使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。
约束条件与数据类型的宽度一样，都是可选参数，主要分为以下几种：
# NOT NULL ：非空约束，指定某列不能为空； # UNIQUE : 唯一约束，指定某列或者几列组合不能重复# PRIMARY KEY ：主键，指定该列的值可以唯一地标识该列记录# FOREIGN KEY ：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性
NOT NULL
是否可空，null表示空，非字符串
not null - 不可空
null - 可空
mysql> create table t12 (id int not null);Query OK, 0 rows affected (0.02 sec)mysql> select * from t12;Empty set (0.00 sec)mysql> desc t12;+-------+---------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id| int(11) | NO| | NULL||+-------+---------+------+-----+---------+-------+row in set (0.00 sec)#不能向id列插入空元素。 mysql> insert into t12 values (null);ERROR 1048 (23000): Column 'id' cannot be nullmysql> insert into t12 values (1);Query OK, 1 row affected (0.01 sec)not null示例
DEFAULT
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
mysql> create table t13 (id1 int not null,id2 int not null default 222);Query OK, 0 rows affected (0.01 sec)mysql> desc t13;+-------+---------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id1| int(11) | NO| | NULL||| id2| int(11) | NO| | 222 ||+-------+---------+------+-----+---------+-------+rows in set (0.01 sec)# 只向id1字段添加值，会发现id2字段会使用默认值填充mysql> insert into t13 (id1) values (111);Query OK, 1 row affected (0.00 sec)mysql> select * from t13;+-----+-----+| id1 | id2 |+-----+-----+| 111 | 222 |+-----+-----+row in set (0.00 sec)# id1字段不能为空，所以不能单独向id2字段填充值；mysql> insert into t13 (id2) values (223);ERROR 1364 (HY000): Field 'id1' doesn't have a default value# 向id1，id2中分别填充数据，id2的填充数据会覆盖默认值mysql> insert into t13 (id1,id2) values (112,223);Query OK, 1 row affected (0.00 sec)mysql> select * from t13;+-----+-----+| id1 | id2 |+-----+-----+| 111 | 222 || 112 | 223 |+-----+-----+rows in set (0.00 sec)not null + default 示例
UNIQUE
唯一约束，指定某列或者几列组合不能重复
方法一：create table department1(id int,name varchar(20) unique,comment varchar(100));方法二：create table department2(id int,name varchar(20),comment varchar(100),unique(name));mysql> insert into department1 values(1,'IT','技术');Query OK, 1 row affected (0.00 sec)mysql> insert into department1 values(1,'IT','技术');ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'unique示例
mysql> create table t1(id int not null unique);Query OK, 0 rows affected (0.02 sec)mysql> desc t1;+-------+---------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id| int(11) | NO| PRI | NULL||+-------+---------+------+-----+---------+-------+row in set (0.00 sec)not null 和unique的结合
create table service(id int primary key auto_increment,name varchar(20),host varchar(15) not null,port int not null,unique(host,port) #联合唯一);mysql> insert into service values-> (1,'nginx','192.168.0.10',80),-> (2,'haproxy','192.168.0.20',80),-> (3,'mysql','192.168.0.30',3306)-> ;Query OK, 3 rows affected (0.01 sec)Records: 3 Duplicates: 0 Waings: 0mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'联合唯一
PRIMARY KEY
主键为了保证表中的每一条数据的该字段都是表格中的唯一值。换言之，它是用来独一无二地确认一个表格中的每一行数据。
主键可以包含一个字段或多个字段。当主键包含多个栏位时，称为组合键 (Composite Key),也可以叫联合主键。
主键可以在建置新表格时设定 (运用 CREATE TABLE 语句)，或是以改变现有的表格架构方式设定 (运用 ALTER TABLE)。
主键必须唯一，主键值非空；可以是单一字段，也可以是多字段组合。
1.单字段主键
============单列做主键===============#方法一：not null+uniquecreate table department1(id int not null unique, #主键name varchar(20) not null unique,comment varchar(100));mysql> desc department1;+---------+--------------+------+-----+---------+-------+| Field| Type | Null | Key | Default | Extra |+---------+--------------+------+-----+---------+-------+| id | int(11) | NO| PRI | NULL||| name| varchar(20) | NO| UNI | NULL||| comment | varchar(100) | YES | | NULL||+---------+--------------+------+-----+---------+-------+rows in set (0.01 sec)#方法二：在某一个字段后用primary keycreate table department2(id int primary key, #主键name varchar(20),comment varchar(100));mysql> desc department2;+---------+--------------+------+-----+---------+-------+| Field| Type | Null | Key | Default | Extra |+---------+--------------+------+-----+---------+-------+| id | int(11) | NO| PRI | NULL||| name| varchar(20) | YES | | NULL||| comment | varchar(100) | YES | | NULL||+---------+--------------+------+-----+---------+-------+rows in set (0.00 sec)#方法三：在所有字段后单独定义primary keycreate table department3(id int,name varchar(20),comment varchar(100),primary key(id); #创建主键并为其命名pk_namemysql> desc department3;+---------+--------------+------+-----+---------+-------+| Field| Type | Null | Key | Default | Extra |+---------+--------------+------+-----+---------+-------+| id | int(11) | NO| PRI | NULL||| name| varchar(20) | YES | | NULL||| comment | varchar(100) | YES | | NULL||+---------+--------------+------+-----+---------+-------+rows in set (0.01 sec)# 方法四：给已经建成的表添加主键约束mysql> create table department4(-> id int,-> name varchar(20),-> comment varchar(100));Query OK, 0 rows affected (0.01 sec)mysql> desc department4;+---------+--------------+------+-----+---------+-------+| Field| Type | Null | Key | Default | Extra |+---------+--------------+------+-----+---------+-------+| id | int(11) | YES | | NULL||| name| varchar(20) | YES | | NULL||| comment | varchar(100) | YES | | NULL||+---------+--------------+------+-----+---------+-------+rows in set (0.01 sec)mysql> alter table department4 modify id int primary key;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Waings: 0mysql> desc department4;+---------+--------------+------+-----+---------+-------+| Field| Type | Null | Key | Default | Extra |+---------+--------------+------+-----+---------+-------+| id | int(11) | NO| PRI | NULL||| name| varchar(20) | YES | | NULL||| comment | varchar(100) | YES | | NULL||+---------+--------------+------+-----+---------+-------+rows in set (0.01 sec)单字段主键
2.多字段主键
==================多列做主键================create table service(ip varchar(15),port char(5),service_name varchar(10) not null,primary key(ip,port));mysql> desc service;+--------------+-------------+------+-----+---------+-------+| Field| Type| Null | Key | Default | Extra |+--------------+-------------+------+-----+---------+-------+| ip| varchar(15) | NO| PRI | NULL||| port | char(5) | NO| PRI | NULL||| service_name | varchar(10) | NO| | NULL||+--------------+-------------+------+-----+---------+-------+rows in set (0.00 sec)mysql> insert into service values-> ('172.16.45.10','3306','mysqld'),-> ('172.16.45.11','3306','mariadb')-> ;Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Waings: 0mysql> insert into service values ('172.16.45.10','3306','nginx');ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'多字段主键
AUTO_INCREMENT
#不指定id，则自动增长create table student(id int primary key auto_increment,name varchar(20),sex enum('male','female') default 'male');mysql> desc student;+-------+-----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+----------------+| id| int(11)| NO| PRI | NULL| auto_increment || name | varchar(20)| YES | | NULL||| sex| enum('male','female') | YES | | male||+-------+-----------------------+------+-----+---------+----------------+mysql> insert into student(name) values-> ('egon'),-> ('alex')-> ;mysql> select * from student;+----+------+------+| id | name | sex |+----+------+------+| 1 | egon | male || 2 | alex | male |+----+------+------+#也可以指定idmysql> insert into student values(4,'asb','female');Query OK, 1 row affected (0.00 sec)mysql> insert into student values(7,'wsb','female');Query OK, 1 row affected (0.00 sec)mysql> select * from student;+----+------+--------+| id | name | sex|+----+------+--------+| 1 | egon | male|| 2 | alex | male|| 4 | asb | female || 7 | wsb | female |+----+------+--------+#对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长mysql> delete from student;Query OK, 4 rows affected (0.00 sec)mysql> select * from student;Empty set (0.00 sec)mysql> insert into student(name) values('ysb');mysql> select * from student;+----+------+------+| id | name | sex |+----+------+------+| 8 | ysb | male |+----+------+------+#应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它mysql> truncate student;Query OK, 0 rows affected (0.01 sec)mysql> insert into student(name) values('egon');Query OK, 1 row affected (0.01 sec)mysql> select * from student;+----+------+------+| id | name | sex |+----+------+------+| 1 | egon | male |+----+------+------+row in set (0.00 sec)设置auto_increment
FOREIKEY
多表 ：
假设我们要描述所有公司的员工，需要描述的属性有这些 ： 工号 姓名 部门
公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费
解决方法： 我们完全可以定义一个部门表 然后让员工信息表关联该表，如何关联，即foreign key
mysql> create table departments (dep_id int(4),dep_name varchar(11));Query OK, 0 rows affected (0.02 sec)mysql> desc departments;+----------+-------------+------+-----+---------+-------+| Field| Type| Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| dep_id| int(4) | YES | | NULL||| dep_name | varchar(11) | YES | | NULL||+----------+-------------+------+-----+---------+-------+rows in set (0.00 sec)# 创建外键不成功mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));ERROR 1215 (HY000): Cannot add foreign key # 设置dep_id非空，仍然不能成功创建外键mysql> alter table departments modify dep_id int(4) not null;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Waings: 0mysql> desc departments;+----------+-------------+------+-----+---------+-------+| Field| Type| Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| dep_id| int(4) | NO| | NULL||| dep_name | varchar(11) | YES | | NULL||+----------+-------------+------+-----+---------+-------+rows in set (0.00 sec)mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));ERROR 1215 (HY000): Cannot add foreign key constraint# 当设置字段为unique唯一字段时，设置该字段为外键成功mysql> alter table departments modify dep_id int(4) unique;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Waings: 0mysql> desc departments;+----------+-------------+------+-----+---------+-------+| Field| Type| Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| dep_id| int(4) | YES | UNI | NULL||| dep_name | varchar(11) | YES | | NULL||+----------+-------------+------+-----+---------+-------+rows in set (0.01 sec)mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));Query OK, 0 rows affected (0.02 sec)创造外键的条件
#表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一create table department(id int primary key,name varchar(20) not null)engine=innodb;#dpt_id外键，关联父表（department主键id），同步更新，同步删除create table employee(id int primary key,name varchar(20) not null,dpt_id int,foreign key(dpt_id)references department(id)on delete cascade # 连级删除on update cascade # 连级更新)engine=innodb;#先往父表department中插入记录insert into department values(1,'教质部'),(2,'技术部'),(3,'人力资源部');#再往子表employee中插入记录insert into employee values(1,'yuan',1),(2,'nezha',2),(3,'egon',2),(4,'alex',2),(5,'wusir',3),(6,'李沁洋',3),(7,'皮卡丘',3),(8,'程咬金',3),(9,'程咬银',3);#删父表department，子表employee中对应的记录跟着删mysql> delete from department where id=2;Query OK, 1 row affected (0.00 sec)mysql> select * from employee;+----+-----------+--------+| id | name | dpt_id |+----+-----------+--------+| 1 | yuan | 1 || 5 | wusir | 3 || 6 | 李沁洋| 3 || 7 | 皮卡丘| 3 || 8 | 程咬金| 3 || 9 | 程咬银| 3 |+----+-----------+--------+rows in set (0.00 sec)#更新父表department，子表employee中对应的记录跟着改mysql> update department set id=2 where id=3;Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Waings: 0mysql> select * from employee;+----+-----------+--------+| id | name | dpt_id |+----+-----------+--------+| 1 | yuan | 1 || 5 | wusir | 2 || 6 | 李沁洋| 2 || 7 | 皮卡丘| 2 || 8 | 程咬金| 2 || 9 | 程咬银| 2 |+----+-----------+--------+rows in set (0.00 sec)外键操作示例
我们总结一下foreign key的下面几个约束作用：
1、先要建立被关联的表才能建立关联表
2、在插入数据记录的时候，要先想被关联表中插入数据，才能往关联表里面插入数据
3、更新或者删除数据的时候，都需要考虑关联表和被关联表的关系
解决方案：
a.删除表的时候，先删除关联表，再删除被关联表
b.重建表的时候，在加外键关联的时候加上这两句：on delete cascade 和 on update cascade
外键约束有三种约束模式（都是针对父表的约束）：
模式一： district 严格约束（默认的 ），父表不能删除或者更新已经被子表数据引用的记录
模式二：cascade 级联模式：父表的操作，对应的子表关联的数据也跟着操作 。
模式三：set null：置空模式，父表操作之后，子表对应的数据（外键字段）也跟着被置空。
通常的一个合理的约束模式是：删除的时候子表置空；更新的时候子表级联。
指定模式的语法：foreign key(外键字段)references 父表(主键字段)on delete 模式 on update 模式;
注意：删除置空的前提条件是 外键字段允许为空，不然外键会创建失败。
外键虽然很强大，能够进行各种约束，但是外键的约束降低了数据的可控性和可拓展性。通常在实际开发时，很少使用外键来约束。
作者：从入门到出师
来源链接：https://www.cnblogs.com/yidashi110/p/9756342.html
版权声明：
1、JavaClub（https://www.javaclub.cn）以学习交流为目的，由作者投稿、网友推荐和小编整理收藏优秀的IT技术及相关内容，包括但不限于文字、图片、音频、视频、软件、程序等,其均来自互联网，本站不享有版权，版权归原作者所有。
2、本站提供的内容仅用于个人学习、研究或欣赏，以及其他非商业性或非盈利性用途，但同时应遵守著作权法及其他相关法律的规定，不得侵犯相关权利人及本网站的合法权利。
3、本网站内容原作者如不愿意在本网站刊登内容，请及时通知本站（javaclubcn@163.com），我们将第一时间核实后及时予以删除。
