主键遵守规则
每个表只能定义一个主键。
主键值必须唯一标识表中的每一行,且不能为 NULL,即表中不可能存在两行数据有相同的主键值。这是唯一性原则。
一个列名只能在复合主键列表中出现一次。
复合主键不能包含不必要的多余列。当把复合主键的某一列删除后,如果剩下的列构成的主键仍然满足唯一性原则,那么这个复合主键是不正确的。这是最小化原则。
特点
自带非空和唯一约束
用来标志当前记录的唯一性,区别于其他记录
一般是单个字段,也可以是联合多个字段
一、如何添加主键
1.建表时添加
1.方法一:列级约束。#直接在字段名和类型后追加create table test1 (id int(10) primary key,uname varchar(20),sex varchar(4),birth year,department varchar(20),address varchar (50),); mysql> desc test1;+------------+-------------+------+-----+---------+-------+| Field| Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| id | int(10)| NO | PRI | NULL | || uname| varchar(20) | YES || NULL | || sex | varchar(4) | YES || NULL | || birth| year(4)| YES || NULL | || department | varchar(20) | YES || NULL | || address | varchar(50) | YES || NULL | |+------------+-------------+------+-----+---------+-------+6 rows in set (0.00 sec)
2.方法二:表级约束#所有字段名和类型完成后添加create table test2 (id int(10),uname varchar(20),sex varchar(4),birth year,department varchar(20),address varchar (50),primary key(id)); mysql> desc test2;+------------+-------------+------+-----+---------+-------+| Field| Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| id | int(10)| NO | PRI | NULL | || uname| varchar(20) | YES || NULL | || sex | varchar(4) | YES || NULL | || birth| year(4)| YES || NULL | || department | varchar(20) | YES || NULL | || address | varchar(50) | YES || NULL | |+------------+-------------+------+-----+---------+-------+6 rows in set (0.00 sec)
2、建表时设置复合主键
create table test3 (id int(10),uname varchar(20),sex varchar(4),birth year,department varchar(20),address varchar (50),primary key(id,uname)); mysql> desc test3;+------------+-------------+------+-----+---------+-------+| Field| Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| id | int(10)| NO | PRI | NULL | || uname| varchar(20) | NO | PRI | NULL | || sex | varchar(4) | YES || NULL | || birth| year(4)| YES || NULL | || department | varchar(20) | YES || NULL | || address | varchar(50) | YES || NULL | |+------------+-------------+------+-----+---------+-------+6 rows in set (0.00 sec)
3、修改表时添加主键
注意:使用alter添加主键前提是该表中没有任何有主键约束。否则会报错
#约束单个字段alter table 表名 add primary key(字段名); #约束多个字段alter table 表名 add primary key(字段名1,字段名2);
例子
先查看test表
desc test;+------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| id| int(10) | NO | PRI | NULL | || uname | varchar(20) | YES | | NULL | || sex | varchar(4) | YES | | NULL | || birth | year(4) | YES | | NULL | || department | varchar(20) | YES | | NULL | || address | varchar(50) | YES | | NULL | |+------------+-------------+------+-----+---------+-------+6 rows in set (0.00 sec)
通过alter给id和uname添加主键
alter table yytest add primary key(id,uname);
再次查看test表
desc test;+------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| id| int(10) | NO | PRI | NULL | || uname | varchar(20) | NO | PRI | NULL | || sex | varchar(4) | YES | | NULL | || birth | year(4) | YES | | NULL | || department | varchar(20) | YES | | NULL | || address | varchar(50) | YES | | NULL | |+------------+-------------+------+-----+---------+-------+6 rows in set (0.00 sec)
二、删除主键
alter table <表名> drop primary key;例子alter table test drop primary key;desc test;+------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| id| int(10) | NO || NULL | || uname | varchar(20) | NO || NULL | || sex | varchar(4) | YES | | NULL | || birth | year(4) | YES | | NULL | || department | varchar(20) | YES | | NULL | || address | varchar(50) | YES | | NULL | |+------------+-------------+------+-----+---------+-------+6 rows in set (0.00 sec)