数据库系统原理与应用教程（029）—— MySQL 的数据完整性（二）：定义主键（primary key）

最新推荐文章于 2023-12-15 22:00:00 发布

睿思达DBA_WGX

最新推荐文章于 2023-12-15 22:00:00 发布

阅读量2.3k

点赞数 6

分类专栏： MySQL基本操作数据库系统原理与应用教程：以MySQL为例文章标签：数据库 mysql java

本文链接：https://blog.csdn.net/weixin_44377973/article/details/125836882

版权

MySQL基本操作同时被 2 个专栏收录

169 篇文章 93 订阅

订阅专栏

数据库系统原理与应用教程：以MySQL为例

83 篇文章 36 订阅

订阅专栏

数据库系统原理与应用教程（029）—— MySQL 的数据完整性（二）：定义主键（primary key）

主键（primary key）是指表中的一个或多个列的组合，其值能够唯一区分表中的每个行。主键用来表示一个特定的行。如果一个表没有定义主键，当对表进行更新操作时将非常不方便，因为在没有主键的情况下，可能无法准确定位要修改的行。

一、定义主键的原则

主键必须满足以下要求：

（1）表中的任两行不能有相同的主键值。

（2）每行都必须具有一个主键值，即：主键列不允许为 NULL。

对主键的要求，最关键的一点是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的。

选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。身份证号、手机号、邮箱地址等这些看上去可以唯一的字段，均不可用作主键。主键最好是完全与业务无关的字段，一般把这个字段命名为 id。

对于大部分应用来说，通常使用自增类型的字段作为主键，数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键。

二、创建表时同时定义主键

1、定义单列主键

如果一个主键只包含一个列，可以使用如下方法定义主键：

create table 表名 (
    <字段名> <数据类型> PRIMARY KEY [AUTO_INCREMENT],
    ....
);

-- 或
create table 表名 (
    <字段定义>... ,
    PRIMARY KEY (字段名)
);

例如：

（1）采用第一种形式

/*
create table t1(
    id int primary key,
    name char(10),
    salary int
);
*/
mysql> create table t1(
    ->     id int primary key,
    ->     name char(10),
    ->     salary int
    -> );
Query OK, 0 rows affected (0.20 sec)

mysql> desc t1;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id     | int(11)  | NO   | PRI | NULL    |       |
| name   | char(10) | YES  |     | NULL    |       |
| salary | int(11)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.06 sec)

（2）采用第二种形式

/*
create table t2(
    id int,
    name char(10),
    salary int,
    primary key(id)
);
*/
mysql> create table t2(
    ->     id int,
    ->     name char(10),
    ->     salary int,
    ->     primary key(id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc t2;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id     | int(11)  | NO   | PRI | NULL    |       |
| name   | char(10) | YES  |     | NULL    |       |
| salary | int(11)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.02 sec)

2、定义多列主键

主键有多个列组成，语法格式如下：

create table 表名 (
    <字段定义>... ,
    PRIMARY KEY (字段1 [,字段2,...])
);

例如：

/*
create table t3(
    s_id int,
    c_id int,
    grade int,
    primary key(s_id, c_id)
);
*/
mysql> create table t3(
    ->     s_id int,
    ->     c_id int,
    ->     grade int,
    ->     primary key(s_id, c_id)
    -> );
Query OK, 0 rows affected (0.12 sec)

mysql> desc t3;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| s_id  | int(11) | NO   | PRI | NULL    |       |
| c_id  | int(11) | NO   | PRI | NULL    |       |
| grade | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.01 sec)

三、为一个表添加主键

1、添加一个列并设置为主键

添加一个新列，同时把该列指定为主键，语法格式为：

alter table 表名 add 列名 类型 primary key [auto_increment];

例如：

（1）添加列之前表中没有数据

/*
create table t11(    
    name char(20),
    salary int
);
*/
mysql> create table t11(    
    ->     name char(20),
    ->     salary int
    -> );
Query OK, 0 rows affected (0.12 sec)

mysql> alter table t11 add id int primary key first;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t11;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id     | int(11)  | NO   | PRI | NULL    |       |
| name   | char(20) | YES  |     | NULL    |       |
| salary | int(11)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

（2）添加新列之前表中已有数据，此时添加主键失败

/*
create table t12(    
    name char(20),
    salary int
);
*/
mysql> create table t12(    
    ->     name char(20),
    ->     salary int
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t12 values('Tom',5200),('Jack',5500);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t12;
+------+--------+
| name | salary |
+------+--------+
| Tom  |   5200 |
| Jack |   5500 |
+------+--------+
2 rows in set (0.00 sec)

-- 插入失败：因为当表中有数据时，新插入的列无法设置为主键。原因是新增的列一定会有重复值
mysql> alter table t12 add id int primary key first;
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

2、把表中的已有列设置为主键

可以把表中已经存在的列定义为主键，语法格式如下：

alter table 表名 add primary key(列名);

例如：

（1）空表或者要设置为主键的列无重复值

/*
create table t13(    
    id int,
    name char(20),
    salary int
);
*/
mysql> create table t13(    
    ->     id int,
    ->     name char(20),
    ->     salary int
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> alter table t13 add primary key(id);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t13;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id     | int(11)  | NO   | PRI | NULL    |       |
| name   | char(20) | YES  |     | NULL    |       |
| salary | int(11)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

/*
create table t14(    
    id int,
    name char(20),
    salary int
);
*/
mysql> insert into t14 values(1,'Tom',5200),(2,'Jack',5500),(3,'Black',4500);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> alter table t14 add primary key(id);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t14;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id     | int(11)  | NO   | PRI | NULL    |       |
| name   | char(20) | YES  |     | NULL    |       |
| salary | int(11)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

（2）主键的列如果有重复值则设置失败

/*
create table t15(    
    id int,
    name char(20),
    salary int
);
*/
mysql> create table t15(    
    ->     id int,
    ->     name char(20),
    ->     salary int
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t15 values(1,'Tom',5200),(1,'Jack',5500),(3,'Black',4500);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> alter table t15 add primary key(id);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

四、删除主键

删除主键约束的语法格式如下：

alter table 表名 drop primary key;

例如：

mysql> desc t11;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id     | int(11)  | NO   | PRI | NULL    |       |
| name   | char(20) | YES  |     | NULL    |       |
| salary | int(11)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show create table t11\G
*************************** 1. row ***************************
       Table: t11
Create Table: CREATE TABLE `t11` (
  `id` int(11) NOT NULL,
  `name` char(20) DEFAULT NULL,
  `salary` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table t11 drop primary key;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t11;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id     | int(11)  | NO   |     | NULL    |       |
| name   | char(20) | YES  |     | NULL    |       |
| salary | int(11)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show create table t11\G
*************************** 1. row ***************************
       Table: t11
Create Table: CREATE TABLE `t11` (
  `id` int(11) NOT NULL,
  `name` char(20) DEFAULT NULL,
  `salary` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)