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  Waings: 0mysql> 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  Waings: 0mysql> 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  Waings: 0mysql> 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  Waings: 0mysql> 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  Waings: 0mysql> 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  Waings: 0mysql> 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  Waings: 0mysql> 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  Waings: 0mysql> 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)