【MySQL】主键约束、唯一键约束以及外键约束的创建与删除
文章目录
一、主键约束
primary key用来唯一约束该字段里面的数据。其主要特征有:
- 不能重复,不能为空
- 一张表中最多只有一个primary key
- primary key 所在列通常是整数类型
1.1 创建主键
(1) 创建表时,直接在字段上指定主键
create table [表名] ([字段1] [字段类型1] primary key,[字段2] [字段类型2], …);
MariaDB [class_info]> create table class_info(id int primary key, name varchar(20) not null, score float(4,2));
Query OK, 0 rows affected (0.02 sec)
MariaDB [class_info]> desc class_info;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| score | float(4,2) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
(2) 创建表时,在所有字段之后使用primary key(字段)
来创建主键。
create table [表名] ([字段1] [字段类型1],[字段2] [字段类型2], … ,primary key(字段));
MariaDB [class_info]> create table class_info(id int, name varchar(20) not null, score float(4,2),primary key(id));
Query OK, 0 rows affected (0.01 sec)
MariaDB [class_info]> desc class_info;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(20) | NO | | NULL | |
| score | float(4,2) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
(3) 当表已经创建好以后,追加主键。
alter table [表名] add primary key(字段);
MariaDB [class_info]> desc class_info;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| name | varchar(20) | NO | | NULL | |
| score | float(4,2) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
MariaDB [class_info]> select * from class_info;
Empty set (0.00 sec)
MariaDB [class_info]> alter table class_info add primary key(id);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [class_info]> desc class_info;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(20) | NO | | NULL | |
| score | float(4,2) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
注意: 如果该字段中的内容有重复或者有空,则会导致追加主键失败。
MariaDB [class_info]> desc student;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default