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)
