【MySQL】主键约束、唯一键约束以及外键约束的创建与删除

【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