MySQL---主键约束（primary key）

主键遵守规则

每个表只能定义一个主键。

主键值必须唯一标识表中的每一行，且不能为 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)