关系型数据库中的一条记录中有若干个属性,若其中某一个属性组
(
注意是组
)
能唯一标识一条记录,该属性组就可以成为一个主键。
主键是每条信息的唯一信息,主键不能重复,主键保证记录的唯一性,主键自动禁止为空值,主键一般标志到无意义的字段上,且主键一般标志到编号字段。
一、声明主键的方法
方式1:创建表的时候为表添加主键
CREATE TABLE tbl_name(
字段名称 字段类型 PRIMARY KEY
);
方式2:更新表结构的时候为表添加主键
ALTER TABLE tbl_name ADD PRIMARY KEY(index_col_name);
mysql> CREATE TABLE tablePK(-> id TINYINT PRIMARY KEY,-> username VARCHAR(20)-> );Query OK, 0 rows affected (0.10 sec)
mysql> DESC tablePK;+----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| id | tinyint(4) | NO | PRI | NULL | || username | varchar(20) | YES | | NULL | |+----------+-------------+------+-----+---------+-------+2 rows in set (0.20 sec)mysql> INSERT tablePK VALUES(1,'张三');mysql> INSERT tablePK VALUES(1,'李四');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' --当主键重复时提示错误
mysql> INSERT tablePK VALUES(NULL,'李四');
ERROR 1048 (23000): Column 'id' cannot be null --当主键为空时提示错误
mysql> INSERT tablePK VALUES(2,'李四');Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tablePK;+----+----------+| id | username |+----+----------+| 1 | 张三 || 2 | 李四 |+----+----------+
二、复合主键
CREATE TABLE tbl_name(
字段描述,
PRIMARY KEY(index_col_name1,
index_col_name2...)
);
mysql> CREATE TABLE IF NOT EXISTS mulPK(-> test1 INT,-> test2 VARCHAR(20),-> test3 TINYINT,-> PRIMARY KEY(test1,test2)-> );Query OK, 0 rows affected (0.22 sec)
mysql> DESC mulPK;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| test1 | int(11) | NO | PRI | 0 | || test2 | varchar(20) | NO | PRI | | || test3 | tinyint(4) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+3 rows in set (0.07 sec)
mysql> SHOW CREATE TABLE mulPK;
| Table | Create Table| mulPK | CREATE TABLE `mulpk` (`test1` int(11) NOT NULL DEFAULT '0',`test2` varchar(20) NOT NULL DEFAULT '',`test3` tinyint(4) DEFAULT NULL,PRIMARY KEY (`test1`,`test2`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
mysql> INSERT mulPK VALUES(1,'A',"11");mysql> INSERT mulPK VALUES(1,'B',"12");mysql> INSERT mulPK VALUES(2,'A',"21");mysql> INSERT mulPK VALUES(2,'B',"22");mysql> INSERT mulPK VALUES(1,'A',"11"); --复合主键都重复时提示错误ERROR 1062 (23000): Duplicate entry '1-A' for key 'PRIMARY'
mysql> SELECT * FROM mulPK;
+-------+-------+-------+
| test1 | test2 | test3 |
+-------+-------+-------+
| 1 | A | 11 |
| 1 | B | 12 |
| 2 | A | 21 |
| 2 | B | 22 |
+-------+-------+-------+