MySQL：创建和删除主键


#MySQL 笔记

建表时指定主键

方式1：

CREATE TABLE `user_info` (
	`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(45) NOT NULL,
	PRIMARY KEY (`id`) -- 指定id列为主键
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE utf8mb4_general_ci;

方式2：

CREATE TABLE `user_info` (
	`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`name` VARCHAR(45) NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE utf8mb4_general_ci;

效果：

mysql> show create table user_info;
+-----------+-----------------------------------------------------+
| Table     | Create Table                                        |
+-----------+-----------------------------------------------------+
| user_info | CREATE TABLE `user_info` (                          |
|           |   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, |
|           |   `name` varchar(45) NOT NULL,                      |
|           |   PRIMARY KEY (`id`)                                |
|           | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4             |
+-----------+-----------------------------------------------------+

建表后添加主键

注意，主键最好在创建表时就添加。且不要删除。

CREATE TABLE `user_info` (
	`id` BIGINT UNSIGNED NOT NULL,
	`name` VARCHAR(45) NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE utf8mb4_general_ci;

让id变成自增主键：

ALTER TABLE `user_info` 
MODIFY COLUMN `id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT;

删除主键

注意，主键最好在创建表时就添加。且不要删除。

建表时候指定主键：

CREATE TABLE `user_info` (
	`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(45) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE utf8mb4_general_ci;

删除主键：

-- 先去掉 AUTO_INCREMENT 属性，因为 AUTO_INCREMENT 字段必须被索引
ALTER TABLE `user_info` 
MODIFY COLUMN `id` BIGINT UNSIGNED NOT NULL;

-- 再去掉主键
ALTER TABLE `user_info` DROP PRIMARY KEY;


( 本文完 )