MySQL删除主键后NOT NULL约束遗留

Question

测试环境：MySQL 8.0.27

测试代码如下：

mysql> use mydb1;
Database changed


mysql> SHOW CREATE TABLE emp5;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| emp5  | CREATE TABLE `emp5` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> ALTER TABLE emp5 ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> SHOW CREATE TABLE emp5;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp5  | CREATE TABLE `emp5` (
  `id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)


mysql> ALTER TABLE emp5 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> SHOW CREATE TABLE emp5;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                    |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| emp5  | CREATE TABLE `emp5` (
  `id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

问题：

原本id字段是DEFAULT NULL，但增删主键后会变成NOT NULL，截然相反了

本以为删除主键后该字段的约束也会被修改回去，但看起来并没有。

Answer 1

最开始创建表的时候ID不是主键，然后等你修改他为主键之后就默认设置为NOT NULL，这设置为NOT NULL是必须的因为主键不能为空所以NOT NULL是强制性的设置，然后等你删除主键依旧会保留原来的约束，只是单纯的删除了主键，并没有什么强制性的要求。