数据库系统原理与应用教程(029)—— MySQL 的数据完整性(二):定义主键(primary key)
目录
数据库系统原理与应用教程(029)—— MySQL 的数据完整性(二):定义主键(primary key)一、定义主键的原则二、创建表时同时定义主键1、定义单列主键2、定义多列主键 三、为一个表添加主键1、添加一个列并设置为主键2、把表中的已有列设置为主键 四、删除主键主键(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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> alter table t14 add primary key(id);Query OK, 0 rows affected (0.08 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> 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: 0mysql> 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: t11Create 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=utf81 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: 0mysql> 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: t11Create Table: CREATE TABLE `t11` (`id` int(11) NOT NULL,`name` char(20) DEFAULT NULL,`salary` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)