MySQL主键约束

主键不能为空(NULL)、不能重复！！

唯一约束不能重复，但可以为空。

1.单主键约束 primary key

mysql> create table user1(
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc user1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

由于id是主键，因此插入的数据不能有相同的id：

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

mysql> insert into user1 values(1,'李四');
ERROR 1062 (23000): Duplicate entry '1' for key 'user1.PRIMARY'

但非主键相同可以：

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

mysql> select *from user1;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 张三   |
+----+--------+
2 rows in set (0.00 sec)

主键不能为空，非主键可以：

mysql> insert into user1 values(NULL,'张三');
ERROR 1048 (23000): Column 'id' cannot be null

mysql> insert into user1 values(3,NULL);
Query OK, 1 row affected (0.00 sec)
mysql> select *from user1;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 张三   |
|  3 | NULL   |
+----+--------+
3 rows in set (0.01 sec)

2.联合主键约束

mysql> create table user2(
    -> id int,
    -> name varchar(20),
    -> password varchar(20),
    -> primary key(id,name)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc user2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(20) | NO   | PRI | NULL    |       |
| password | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

添加数据，只要主键的两项不全一致就行：

mysql> insert into user2 values(1,'张三','123');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user2 values(2,'张三','123');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user2 values(1,'李四','123');
Query OK, 1 row affected (0.00 sec)

mysql> select *from user2;
+----+--------+----------+
| id | name   | password |
+----+--------+----------+
|  1 | 张三   | 123      |
|  1 | 李四   | 123      |
|  2 | 张三   | 123      |
+----+--------+----------+
3 rows in set (0.00 sec)

但主键不能为空，任何一个都不行：

mysql> insert into user2 values(NULL,'李四','123');
ERROR 1048 (23000): Column 'id' cannot be null

3.自增约束 auto increment

mysql> create table user3(
    -> id int primary key auto_increment,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc user3;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

仅添加非主键部分，id默认从1开始：

mysql> insert into user3(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user3(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user3(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> select *from user3;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | zhangsan |
|  3 | zhangsan |
+----+----------+
3 rows in set (0.00 sec)

如果对id有定义，可以仅添加一次：

mysql> delete from user3 where name='zhangsan';
Query OK, 3 rows affected (0.00 sec)

mysql> insert into user3 values(202201,'zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user3(name) values('zhangsan');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user3(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> select *from user3;
+--------+----------+
| id     | name     |
+--------+----------+
| 202201 | zhangsan |
| 202202 | zhangsan |
| 202203 | zhangsan |
+--------+----------+
3 rows in set (0.00 sec)

4.建表后添加与删除主键

mysql> create table user4(
    -> id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

添加主键：

mysql> alter table user4 add primary key(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

删除主键：

mysql> alter table user4 drop primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

添加和删除联合主键一样的道理：

mysql> alter table user4 add primary key(id,name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table user4 drop primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

修改主键约束，和添加主键差不多，一般不用这种方法：

mysql> alter table user4 modify id int primary key;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

5.唯一约束------约束修饰的字段的值不可以重复(可以为空)

5.1建表后添加

mysql> create table user5(
    -> id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> alter table user5 add unique(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

添加数据(唯一约束下的name字段不能重复)：

mysql> insert into user5 values(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user5 values(1,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'user5.name'
mysql> insert into user5 values(1,'lisi');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user5 values(1,'NULL');
Query OK, 1 row affected (0.00 sec)

mysql> select *from user5;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    1 | lisi     |
|    1 | NULL     |
+------+----------+
3 rows in set (0.00 sec)

5.2建表时添加

mysql> create table user6(
    -> id int,
    -> name varchar(20),
    -> unique(name)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc user6;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

或

mysql> create table user7(
    -> id int,
    -> name varchar(20) unique
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

5.3添加多个唯一约束(依旧是两个不完全一样就行)

mysql> create table user8(
    -> id int,
    -> name varchar(20),
    -> unique(id,name)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc user8;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  | MUL | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into user8 values(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user8 values(2,'zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> select *from user8;
+------+----------+
| id   | name     |
+------+----------+
| NULL | NULL     |
|    1 | zhangsan |
|    2 | zhangsan |
+------+----------+
3 rows in set (0.01 sec)

5.4删除唯一约束

mysql> alter table user7 drop index name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

5.5修改唯一约束

mysql> alter table user7 modify name varchar(20) unique;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

6.非空约束 not null

mysql> create table user9(
    -> id int,
    -> name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc user9;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into user9(id) values(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

mysql> insert into user9 values(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user9(name) values('lisi');
Query OK, 1 row affected (0.00 sec)

mysql> select *from user9;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
| NULL | lisi     |
+------+----------+
2 rows in set (0.00 sec)

7.默认约束--------当我们插入字段值的时候，如果没有传值，就会使用默认值

mysql> insert into user10(id,name) values(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> select *from user10;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   10 |
+------+----------+------+
1 row in set (0.00 sec)

mysql> insert into user10 values(1,'zhangsan',19);
Query OK, 1 row affected (0.00 sec)

mysql> select *from user10;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   10 |
|    1 | zhangsan |   19 |
+------+----------+------+
2 rows in set (0.00 sec)

8.外键约束----------涉及到两个表(主表、副表)

mysql> create table classes(
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table students(
    -> id int primary key,
    -> name varchar(20),
    -> class_id int,
    -> foreign key(class_id) references classes(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc classes;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| class_id | int         | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into classes values(1,'1班');
Query OK, 1 row affected (0.00 sec)
mysql> insert into classes values(2,'2班');
Query OK, 1 row affected (0.00 sec)
mysql> insert into classes values(3,'3班');
Query OK, 1 row affected (0.00 sec)
mysql> insert into classes values(4,'4班');
Query OK, 1 row affected (0.00 sec)

mysql> select *from classes;
+----+------+
| id | name |
+----+------+
|  1 | 1班  |
|  2 | 2班  |
|  3 | 3班  |
|  4 | 4班  |
+----+------+
4 rows in set (0.00 sec)

--主表classes中没有的数据值，在副表中是不可以使用的

--主表中的记录被副表引用，是不可以被删除的

mysql> insert into students values(1001,'张三',1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into students values(1002,'张三',2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into students values(1003,'张三',3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into students values(1004,'张三',4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into students values(1005,'张三',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))

mysql> select *from students;
+------+--------+----------+
| id   | name   | class_id |
+------+--------+----------+
| 1001 | 张三   |        1 |
| 1002 | 张三   |        2 |
| 1003 | 张三   |        3 |
| 1004 | 张三   |        4 |
+------+--------+----------+
4 rows in set (0.00 sec)

MySQL主键约束

推荐阅读