MySQL表的完整性约束

表的完整性约束

为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。

　　约束条件与数据类型的宽度一样，都是可选参数，主要分为以下几种：

1# NOT NULL ：非空约束，指定某列不能为空； 
2# UNIQUE : 唯一约束，指定某列或者几列组合不能重复
3# PRIMARY KEY ：主键，指定该列的值可以唯一地标识该列记录
4# FOREIGN KEY ：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性

NOT NULL

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

not null示例：

1mysql> create table t12 (id int not null);
2Query OK, 0 rows affected (0.02 sec)
3
4mysql> select * from t12;
5Empty set (0.00 sec)
6
7mysql> desc t12;
8+-------+---------+------+-----+---------+-------+
9| Field | Type    | Null | Key | Default | Extra |
10+-------+---------+------+-----+---------+-------+
11| id    | int(11) | NO   |     | NULL    |       |
12+-------+---------+------+-----+---------+-------+
131 row in set (0.00 sec)
14
15#不能向id列插入空元素。 
16mysql> insert into t12 values (null);
17ERROR 1048 (23000): Column 'id' cannot be null
18
19mysql> insert into t12 values (1);
20Query OK, 1 row affected (0.01 sec)

DEFAULT

我们约束某一列不为空，如果这一列中经常有重复的内容，就需要我们频繁的插入，这样会给我们的操作带来新的负担，于是就出现了默认值的概念。

默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

not null + default 示例：

1mysql> create table t13 (id1 int not null,id2 int not null default 222);
2Query OK, 0 rows affected (0.01 sec)
3
4mysql> desc t13;
5+-------+---------+------+-----+---------+-------+
6| Field | Type    | Null | Key | Default | Extra |
7+-------+---------+------+-----+---------+-------+
8| id1   | int(11) | NO   |     | NULL    |       |
9| id2   | int(11) | NO   |     | 222     |       |
10+-------+---------+------+-----+---------+-------+
112 rows in set (0.01 sec)
12
13# 只向id1字段添加值，会发现id2字段会使用默认值填充
14mysql> insert into t13 (id1) values (111);
15Query OK, 1 row affected (0.00 sec)
16
17mysql> select * from t13;
18+-----+-----+
19| id1 | id2 |
20+-----+-----+
21| 111 | 222 |
22+-----+-----+
231 row in set (0.00 sec)
24
25# id1字段不能为空，所以不能单独向id2字段填充值；
26mysql> insert into t13 (id2) values (223);
27ERROR 1364 (HY000): Field 'id1' doesn't have a default value
28
29# 向id1，id2中分别填充数据，id2的填充数据会覆盖默认值
30mysql> insert into t13 (id1,id2) values (112,223);
31Query OK, 1 row affected (0.00 sec)
32
33mysql> select * from t13;
34+-----+-----+
35| id1 | id2 |
36+-----+-----+
37| 111 | 222 |
38| 112 | 223 |
39+-----+-----+
402 rows in set (0.00 sec)

not null不生效解决办法：

1设置严格模式：
2    不支持对not null字段插入null值
3    不支持对自增长字段插入”值
4    不支持text字段有默认值
5
6直接在mysql中生效(重启失效):
7mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
8
9配置文件添加(永久失效)：
10sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

UNIQUE

唯一约束，指定某列或者几列组合不能重复

unique示例：

1方法一：
2create table department1(
3id int,
4name varchar(20) unique,
5comment varchar(100)
6);
7
8
9方法二：
10create table department2(
11id int,
12name varchar(20),
13comment varchar(100),
14unique(name)
15);
16
17
18mysql> insert into department1 values(1,'IT','技术');
19Query OK, 1 row affected (0.00 sec)
20mysql> insert into department1 values(1,'IT','技术');
21ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'

not null 和unique的结合：

1mysql> create table t1(id int not null unique);
2Query OK, 0 rows affected (0.02 sec)
3
4mysql> desc t1;
5+-------+---------+------+-----+---------+-------+
6| Field | Type    | Null | Key | Default | Extra |
7+-------+---------+------+-----+---------+-------+
8| id    | int(11) | NO   | PRI | NULL    |       |
9+-------+---------+------+-----+---------+-------+
101 row in set (0.00 sec)

联合唯一：

1create table service(
2id int primary key auto_increment,
3name varchar(20),
4host varchar(15) not null,
5port int not null,
6unique(host,port) #联合唯一
7);
8
9mysql> insert into service values
10    -> (1,'nginx','192.168.0.10',80),
11    -> (2,'haproxy','192.168.0.20',80),
12    -> (3,'mysql','192.168.0.30',3306)
13    -> ;
14Query OK, 3 rows affected (0.01 sec)
15Records: 3  Duplicates: 0  Warnings: 0
16
17mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);
18ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'

PRIMARY KEY

主键为了保证表中的每一条数据的该字段都是表格中的唯一值。换言之，它是用来独一无二地确认一个表格中的每一行数据。 主键可以包含一个字段或多个字段。当主键包含多个栏位时，称为组合键 (Composite Key),也可以叫联合主键。 主键可以在建置新表格时设定 (运用 CREATE TABLE 语句)，或是以改变现有的表格架构方式设定 (运用 ALTER TABLE)。 主键必须唯一，主键值非空；可以是单一字段，也可以是多字段组合。

1.单字段主键

1============单列做主键===============
2#方法一：not null+unique
3create table department1(
4id int not null unique, #主键
5name varchar(20) not null unique,
6comment varchar(100)
7);
8
9mysql> desc department1;
10+---------+--------------+------+-----+---------+-------+
11| Field   | Type         | Null | Key | Default | Extra |
12+---------+--------------+------+-----+---------+-------+
13| id      | int(11)      | NO   | PRI | NULL    |       |
14| name    | varchar(20)  | NO   | UNI | NULL    |       |
15| comment | varchar(100) | YES  |     | NULL    |       |
16+---------+--------------+------+-----+---------+-------+
17rows in set (0.01 sec)
18
19#方法二：在某一个字段后用primary key
20create table department2(
21id int primary key, #主键
22name varchar(20),
23comment varchar(100)
24);
25
26mysql> desc department2;
27+---------+--------------+------+-----+---------+-------+
28| Field   | Type         | Null | Key | Default | Extra |
29+---------+--------------+------+-----+---------+-------+
30| id      | int(11)      | NO   | PRI | NULL    |       |
31| name    | varchar(20)  | YES  |     | NULL    |       |
32| comment | varchar(100) | YES  |     | NULL    |       |
33+---------+--------------+------+-----+---------+-------+
34rows in set (0.00 sec)
35
36#方法三：在所有字段后单独定义primary key
37create table department3(
38id int,
39name varchar(20),
40comment varchar(100),
41primary key(id); #创建主键并为其命名pk_name
42
43mysql> desc department3;
44+---------+--------------+------+-----+---------+-------+
45| Field   | Type         | Null | Key | Default | Extra |
46+---------+--------------+------+-----+---------+-------+
47| id      | int(11)      | NO   | PRI | NULL    |       |
48| name    | varchar(20)  | YES  |     | NULL    |       |
49| comment | varchar(100) | YES  |     | NULL    |       |
50+---------+--------------+------+-----+---------+-------+
51rows in set (0.01 sec)
52
53# 方法四：给已经建成的表添加主键约束
54mysql> create table department4(
55    -> id int,
56    -> name varchar(20),
57    -> comment varchar(100));
58Query OK, 0 rows affected (0.01 sec)
59
60mysql> desc department4;
61+---------+--------------+------+-----+---------+-------+
62| Field   | Type         | Null | Key | Default | Extra |
63+---------+--------------+------+-----+---------+-------+
64| id      | int(11)      | YES  |     | NULL    |       |
65| name    | varchar(20)  | YES  |     | NULL    |       |
66| comment | varchar(100) | YES  |     | NULL    |       |
67+---------+--------------+------+-----+---------+-------+
683 rows in set (0.01 sec)
69
70mysql> alter table department4 modify id int primary key;
71Query OK, 0 rows affected (0.02 sec)
72Records: 0  Duplicates: 0  Warnings: 0
73
74mysql> desc department4;
75+---------+--------------+------+-----+---------+-------+
76| Field   | Type         | Null | Key | Default | Extra |
77+---------+--------------+------+-----+---------+-------+
78| id      | int(11)      | NO   | PRI | NULL    |       |
79| name    | varchar(20)  | YES  |     | NULL    |       |
80| comment | varchar(100) | YES  |     | NULL    |       |
81+---------+--------------+------+-----+---------+-------+
823 rows in set (0.01 sec)

2.多字段主键

1==================多列做主键================
2create table service(
3ip varchar(15),
4port char(5),
5service_name varchar(10) not null,
6primary key(ip,port)
7);
8
9
10mysql> desc service;
11+--------------+-------------+------+-----+---------+-------+
12| Field        | Type        | Null | Key | Default | Extra |
13+--------------+-------------+------+-----+---------+-------+
14| ip           | varchar(15) | NO   | PRI | NULL    |       |
15| port         | char(5)     | NO   | PRI | NULL    |       |
16| service_name | varchar(10) | NO   |     | NULL    |       |
17+--------------+-------------+------+-----+---------+-------+
183 rows in set (0.00 sec)
19
20mysql> insert into service values
21    -> ('172.16.45.10','3306','mysqld'),
22    -> ('172.16.45.11','3306','mariadb')
23    -> ;
24Query OK, 2 rows affected (0.00 sec)
25Records: 2  Duplicates: 0  Warnings: 0
26
27mysql> insert into service values ('172.16.45.10','3306','nginx');
28ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'
29

AUTO_INCREMENT

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

1#不指定id，则自动增长
2create table student(
3id int primary key auto_increment,
4name varchar(20),
5sex enum('male','female') default 'male'
6);
7
8mysql> desc student;
9+-------+-----------------------+------+-----+---------+----------------+
10| Field | Type                  | Null | Key | Default | Extra          |
11+-------+-----------------------+------+-----+---------+----------------+
12| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
13| name  | varchar(20)           | YES  |     | NULL    |                |
14| sex   | enum('male','female') | YES  |     | male    |                |
15+-------+-----------------------+------+-----+---------+----------------+
16mysql> insert into student(name) values
17    -> ('egon'),
18    -> ('alex')
19    -> ;
20
21mysql> select * from student;
22+----+------+------+
23| id | name | sex  |
24+----+------+------+
25|  1 | egon | male |
26|  2 | alex | male |
27+----+------+------+
28
29
30#也可以指定id
31mysql> insert into student values(4,'asb','female');
32Query OK, 1 row affected (0.00 sec)
33
34mysql> insert into student values(7,'wsb','female');
35Query OK, 1 row affected (0.00 sec)
36
37mysql> select * from student;
38+----+------+--------+
39| id | name | sex    |
40+----+------+--------+
41|  1 | egon | male   |
42|  2 | alex | male   |
43|  4 | asb  | female |
44|  7 | wsb  | female |
45+----+------+--------+
46
47
48#对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
49mysql> delete from student;
50Query OK, 4 rows affected (0.00 sec)
51
52mysql> select * from student;
53Empty set (0.00 sec)
54
55mysql> insert into student(name) values('ysb');
56mysql> select * from student;
57+----+------+------+
58| id | name | sex  |
59+----+------+------+
60|  8 | ysb  | male |
61+----+------+------+
62
63#应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
64mysql> truncate student;
65Query OK, 0 rows affected (0.01 sec)
66
67mysql> insert into student(name) values('egon');
68Query OK, 1 row affected (0.01 sec)
69
70mysql> select * from student;
71+----+------+------+
72| id | name | sex  |
73+----+------+------+
74|  1 | egon | male |
75+----+------+------+
76row in set (0.00 sec)
77

FOREI KEY

多表 ：

假设我们要描述所有公司的员工，需要描述的属性有这些 ： 工号 姓名 部门

公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费

解决方法： 我们完全可以定义一个部门表 然后让员工信息表关联该表，如何关联，即foreign key

创造外键的条件：

1mysql> create table departments (dep_id int(4),dep_name varchar(11));
2Query OK, 0 rows affected (0.02 sec)
3
4mysql> desc departments;
5+----------+-------------+------+-----+---------+-------+
6| Field    | Type        | Null | Key | Default | Extra |
7+----------+-------------+------+-----+---------+-------+
8| dep_id   | int(4)      | YES  |     | NULL    |       |
9| dep_name | varchar(11) | YES  |     | NULL    |       |
10+----------+-------------+------+-----+---------+-------+
112 rows in set (0.00 sec)
12
13# 创建外键不成功
14mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
15ERROR 1215 (HY000): Cannot add foreign key 
16
17# 设置dep_id非空，仍然不能成功创建外键
18mysql> alter table departments modify dep_id int(4) not null;
19Query OK, 0 rows affected (0.02 sec)
20Records: 0  Duplicates: 0  Warnings: 0
21
22mysql> desc departments;
23+----------+-------------+------+-----+---------+-------+
24| Field    | Type        | Null | Key | Default | Extra |
25+----------+-------------+------+-----+---------+-------+
26| dep_id   | int(4)      | NO   |     | NULL    |       |
27| dep_name | varchar(11) | YES  |     | NULL    |       |
28+----------+-------------+------+-----+---------+-------+
292 rows in set (0.00 sec)
30
31mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
32ERROR 1215 (HY000): Cannot add foreign key constraint
33
34# 当设置字段为unique唯一字段时，设置该字段为外键成功
35mysql> alter table departments modify dep_id int(4) unique;
36Query OK, 0 rows affected (0.01 sec)
37Records: 0  Duplicates: 0  Warnings: 0
38
39mysql> desc departments;                                                                                                       +----------+-------------+------+-----+---------+-------+
40| Field    | Type        | Null | Key | Default | Extra |
41+----------+-------------+------+-----+---------+-------+
42| dep_id   | int(4)      | YES  | UNI | NULL    |       |
43| dep_name | varchar(11) | YES  |     | NULL    |       |
44+----------+-------------+------+-----+---------+-------+
452 rows in set (0.01 sec)
46
47mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
48Query OK, 0 rows affected (0.02 sec)
49

外键操作示例：

1#表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一
2create table department(
3id int primary key,
4name varchar(20) not null
5)engine=innodb;
6
7#dpt_id外键，关联父表（department主键id），同步更新，同步删除
8create table employee(
9id int primary key,
10name varchar(20) not null,
11dpt_id int,
12foreign key(dpt_id)
13references department(id)
14on delete cascade  # 级连删除
15on update cascade # 级连更新
16)engine=innodb;
17
18
19#先往父表department中插入记录
20insert into department values
21(1,'教质部'),
22(2,'技术部'),
23(3,'人力资源部');
24
25
26#再往子表employee中插入记录
27insert into employee values
28(1,'yuan',1),
29(2,'nezha',2),
30(3,'egon',2),
31(4,'alex',2),
32(5,'wusir',3),
33(6,'李沁洋',3),
34(7,'皮卡丘',3),
35(8,'程咬金',3),
36(9,'程咬银',3)
37;
38
39
40#删父表department，子表employee中对应的记录跟着删
41mysql> delete from department where id=2;
42Query OK, 1 row affected (0.00 sec)
43
44mysql> select * from employee;
45+----+-----------+--------+
46| id | name      | dpt_id |
47+----+-----------+--------+
48|  1 | yuan      |      1 |
49|  5 | wusir     |      3 |
50|  6 | 李沁洋    |      3 |
51|  7 | 皮卡丘    |      3 |
52|  8 | 程咬金    |      3 |
53|  9 | 程咬银    |      3 |
54+----+-----------+--------+
556 rows in set (0.00 sec)
56
57
58#更新父表department，子表employee中对应的记录跟着改
59mysql> update department set id=2 where id=3;
60Query OK, 1 row affected (0.01 sec)
61Rows matched: 1  Changed: 1  Warnings: 0
62
63mysql> select * from employee;
64+----+-----------+--------+
65| id | name      | dpt_id |
66+----+-----------+--------+
67|  1 | yuan      |      1 |
68|  5 | wusir     |      2 |
69|  6 | 李沁洋    |      2 |
70|  7 | 皮卡丘    |      2 |
71|  8 | 程咬金    |      2 |
72|  9 | 程咬银    |      2 |
73+----+-----------+--------+
746 rows in set (0.00 sec)
75

on delete(了解)：

1  . cascade方式
2在父表上update/delete记录时，同步update/delete掉子表的匹配记录 
3
4   . set null方式
5在父表上update/delete记录时，将子表上匹配记录的列设为null
6要注意子表的外键列不能为not null  
7
8   . No action方式
9如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作  
10
11   . Restrict方式
12同no action, 都是立即检查外键约束
13
14   . Set default方式
15父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别