【MySQL】表的约束（二）

一. 主键

主键primary key
用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表最多只能有一个主键，主键所在的列通常是整数类型

• 创建表时直接在字段上指定主键

mysql> create table student(
    -> id int primary key comment '学生id，唯一且非空',
    -> name varchar(6)
    -> );

mysql> desc student;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | NO   | PRI | NULL    |       |   <= key中显示PRI
| name  | varchar(6) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1
2
3
4
5
6
7
8
9
10
11
12

• 主键约束：非空且唯一

mysql> insert into student values (1,'张三');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values (1,'李四');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
1
2
3
4
5

• 可以在建表后追加主键

alter table 表名 add primary key(属性名);

• 删除主键

alter table 表名 drop primary key;

---

• 复合主键
  虽然一张表只能有一个主键，但是一个主键可以是多个属性

mysql> create table student(
    -> id int,
    -> name varchar(6),
    -> primary key(id,name)  //id和name的复合主键
    -> );
 
mysql> desc student;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | NO   | PRI | NULL    |       |
| name  | varchar(6) | NO   | PRI | NULL    |       |
+-------+------------+------+-----+---------+-------+
1
2
3
4
5
6
7
8
9
10
11
12
13

如此，id和name都不允许为空，且不能重复（两个都相同才算重复）

二. 自增长

auto_increment：当对应的字段不显示赋值时，系统会赋值为当前字段中已经有的最大值+1。通常和主键搭配使用

自增长的特点：

• 任何一个字段是自增长，前提是本身是一个索引（key一栏有值）
• 自增长字段必须是整数
• 一张表最多只能有一个自增长

mysql> create table student(
    -> id int unsigned primary key auto_increment,
    -> name varchar(6)
    -> );

mysql> show create table student \G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> insert into student (name) values ('张三');
mysql> insert into student (name) values ('李四');
mysql> insert into student (name) values ('王五');

mysql> select * from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王五   |
+----+--------+

mysql> show create table student \G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

创建表时，如果没有指定自增长的起始值，默认为1
每次插入数据后，MySQL会保存当前自增长属性的最大值+1，供下次插入使用

如果此时插入一个id=500的，那么MySQL保存的就是501

mysql> insert into student (id,name) values (500,'赵六');

mysql> show create table student \G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=501 DEFAULT CHARSET=utf8
1
2
3
4
5
6
7
8
9
10

• 建表时还可以指定auto_increment的起始值

mysql> create table student(
    -> id int unsigned primary key auto_increment,
    -> name varchar(6)
    -> )auto_increment=500;  //指定自增长的起始值
1
2
3
4

三. 唯一值

一张表往往有很多字段需要唯一性，数据不能重复，但是一张表只能有一个主键；唯一键就可以解决表中多个字段需要唯一性约束的问题

唯一键和主键本质差不多，唯一键允许为空，而且可以多个为空，空字段不作唯一性比较

应用场景：

员工号和身份证。员工号作主键不允许为空且唯一，但身份证同样不允许重复，但又不能和员工号组成复合主键，所以可以使用唯一键

mysql> create table worker(
    -> id int unsigned primary key auto_increment comment '员工工号',
    -> telephone int unique comment '员工电话号',
    -> name varchar(6) comment '员工姓名'
    -> );

mysql> insert into worker (telephone,name) values (123456,'张三');
mysql> insert into worker (telephone,name) values (123456,'李四');
ERROR 1062 (23000): Duplicate entry '123456' for key 'telephone'

mysql> insert into worker (telephone,name) values (NULL,'李四');
mysql> insert into worker (telephone,name) values (NULL,'王五');

mysql> select * from worker;
+----+-----------+--------+
| id | telephone | name   |
+----+-----------+--------+
|  1 |    123456 | 张三   |
|  3 |      NULL | 李四   |
|  4 |      NULL | 王五   |
+----+-----------+--------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

四. 外键

外键用于定义主表和从表之间的关系；外键约束主要定义在从表上，主表则必须有主键约束或唯一键约束。当定义外键后，要求外键列数据必须在主表中存在或为NULL

foreign key（字段名） references 主表（字段名）

案例：

如此，课程表为主表，学生表为从表

mysql> create table course(
    -> id int unsigned primary key,
    -> name varchar(6)
    -> );
mysql> create table student( 
	->id int unsigned primary key,
	->name varchar(6),
	->class_id int unsigned,
	->foreign key (class_id) references coursse(id)
	->);
1
2
3
4
5
6
7
8
9
10

注意点：

• 不允许在从表的外键中插入不存在于主表的数据
• 主表对应外键，在从表中有数据时，不允许删除该外键

mysql> insert into course values (001,'高数课');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values (100,'张三',001);
Query OK, 1 row affected (0.00 sec)

//不允许在从表的外键中插入不存在于主表的数据
mysql> insert into student values (200,'李四',002);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint 
fails (`test_demo`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) 
REFERENCES `course` (`id`))
//主表对应外键，在从表中有数据时，不允许删除该外键
mysql> delete from table course where id=001;
ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use
near 'table course where id=001' at line 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

结束语

感谢你的阅读

如果觉得本篇文章对你有所帮助的话，不妨点个赞支持一下博主，拜托啦，这对我真的很重要。