MySQL中常见的约束（Constraints）

0.约束的概念

SQL 约束用于规定表中的数据规则，如果存在违反约束的数据行为，行为会被约束终止。

1.主键约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录。要求必须包含唯一的值且不为空，每个表有且仅有一个主键。

（转自：https://www.cnblogs.com/lwj0126/p/16533946.html）

自然主键：主键值最好就是一个和业务没有任何关系的自然数。（推荐），可使用主键值自增（auto_increment）方式。

业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐）

最好不要拿和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。

例1：在创建表时创建主键约束

方式一：

use students;
create table Persons
(
  P_id int not null primary key,
  P_Name varchar(50),
  City varchar(30)
)

方式二：

use students;
create table Persons
(
  P_id int not null,
  P_Name varchar(50),
  City varchar(30),
  primary key(P_id)
)

 例2：在一张表中创建多个主键约束

/*在一张表中定义多个主键*/
use students;
drop table if EXISTS Persons;
create table Persons
(
  P_id int not null,
  P_Name varchar(50),
  City varchar(30),
  CONSTRAINT pk_PersonID_Name PRIMARY key(P_id,P_Name)
);
desc Persons;

语法：CONSTRAINT 主键约束的名称 PRIMARY key(字段1，字段2，....)
在表创建以后，使用alter table创建主键约束
例3：alter table添加单个主键约束

alter table Persons add primary key(P_id);

例4：alter table添加多个主键约束

alter table Persons add CONSTRAINT pk_PersonID_Name primary key(P_id,P_Name);

注意：（1）如果之前已经创建过主键的，先将原来的主键删除，alter TABLE Persons drop primary key;

           （2）CONSTRAINT关键字后面的主键约束的名字可以自定义。

撤销primary key约束，注意，如果表中有自动增长的主键，需要先撤销自动增长才能删除主键约束。

alter TABLE Persons drop primary key;

 2.not null约束

NOT NULL 约束强制列不接受 NULL 值，如果不向字段添加值，就无法插入新记录或者更新记录。

例5：先修改上面的Persons表，给City列添加非空约束。

alter table Persons MODIFY column City varchar(30) not null;

此时如果在表中新增记录，则City列不能为空，否则报错：

  删除not null约束

alter table Persons MODIFY column City varchar(30) null;

 3.unique约束

唯一性约束，它可以使某个字段的值不能重复，比如email不能重复。

例5：给Persons表添加一个email字段，并设置字段为unique约束

alter table Persons add COLUMN email varchar(50) UNIQUE;

如果列的值重复，则报错

 

注意：unique约束与主键约束的相同点：为列或列的集合提供唯一的约束规则；不同点：主键约束不能为空，而unique约束在一张表中可以有多个，且可以为空。

 可以在创建表的时候添加唯一约束，与添加主键约束语法一致，如：

drop table if EXISTS Persons;
create table Persons
(
  P_id int not null,
  P_Name varchar(50),
  City varchar(30),
  email varchar(50),
  UNIQUE(email),
  CONSTRAINT pk_PersonID_Name PRIMARY key(P_id,P_Name)
);
desc Persons;

drop table if EXISTS Persons;
create table Persons
(
  P_id int not null,
  P_Name varchar(50),
  City varchar(30),
  email varchar(50),
  ID varchar(15) COMMENT '身份证号',
  CONSTRAINT uq_Person_ID UNIQUE(email,ID),
  CONSTRAINT pk_PersonID_Name PRIMARY key(P_id,P_Name)
);
desc Persons;

创建unique约束成功

 4.外键约束（转自：http://c.biancheng.net/view/2441.html）

      MySQL外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。
     外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。比如，一个水果摊，只有苹果、桃子、李子、西瓜等 4 种水果，那么，你来到水果摊要买水果就只能选择苹果、桃子、李子和西瓜，其它的水果都是不能购买的。
      主表删除某条记录时，从表中与之对应的记录也必须有相应的改变。一个表可以有一个或多个外键，外键可以为空值，若不为空值，则每一个外键的值必须等于主表中主键的某个值。
定义外键时，需要遵守下列规则：

• 主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
• 必须为主表定义主键。
• 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
• 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
• 外键中列的数目必须和主表的主键中列的数目相同。
• 外键中列的数据类型必须和主表主键中对应列的数据类型相同。

在students数据库中增加courses(课程表)，stu_cou（选课表），修改person表的表名为stuInfo。

use students;
/*课程表*/
create table courses
(
 C_id int not null primary key,
 C_Name varchar(30),
 C_Credit TINYINT
 );
 /*修改表名称*/
alter table person rename to stuInfo;
/*选课表*/
create table stu_cou
(
 ID int not null auto_increment primary key,
 P_id int not null,
 C_id int not null
 );

 例6：使用alter table为stu_cou表添加一个名为fk_pid的外键约束，让stu_cou表中的P_id字段作为外键关联到主表stuInfo表的主键P_id。

 /*添加了一个基于学号的外键约束*/
 alter table stu_cou add CONSTRAINT fk_pid foreign key(P_id) REFERENCES stuInfo(P_id);

此时stuInfo中的数据如下：

  courses表中的数据如下：

  在stu_cou表中添加数据如下：

此时P_id列的数据与stuInfo表中的数据不一致，没有学号为2002的记录，报错如下：

 同理，为stu_cou表添加一个名为fk_cid的外键约束，让stu_cou表中的C_id字段作为外键关联到主表courses表的主键C_id。

  /*添加了一个基于课程号的外键约束*/
 alter table stu_cou add CONSTRAINT fk_cid foreign key(C_id) REFERENCES courses(C_id);

完成上述步骤后，主表中信息的更新和删除，从表无法同时更新和删除，还需要添加级联功能。

例7：为stu_cou表的两个外键添加外键约束和级联操作

 /*添加级联更新和删除操作*/
 /*1.先删除原来的外键*/
 ALTER table stu_cou drop FOREIGN key fk_pid;
 ALTER table stu_cou drop FOREIGN key fk_cid;
 /*2.重新添加外键约束*/
 alter table stu_cou add CONSTRAINT fk_pid foreign key(P_id) REFERENCES stuInfo(P_id) ON UPDATE CASCADE ON DELETE CASCADE ;
 alter table stu_cou add CONSTRAINT fk_cid foreign key(C_id) REFERENCES courses(C_id) ON UPDATE CASCADE ON DELETE CASCADE ;

 添加级联操作后，主表信息的改变，从表对应的数据会相应改变。

例8：在创建stu_cou表时就为P_id ,C_id 添加外键约束和级联关系。

语法：[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]

 use students;
 drop table if EXISTS stu_cou;
 create table stu_cou
(
 ID int not null auto_increment primary key,
 P_id int not null,
 C_id int not null,
  CONSTRAINT fk_pid foreign key(P_id) REFERENCES stuInfo(P_id) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_cid foreign key(C_id) REFERENCES courses(C_id) ON UPDATE CASCADE ON DELETE CASCADE
 );

 5.默认值约束（DEFAULT）转自：http://c.biancheng.net/view/2447.html

用来指定某列的默认值。在表中插入一条新记录时，如果没有为某个字段赋值，系统就会自动为这个字段插入默认值。

创建表时创建默认值约束的语法：<字段名> <数据类型> DEFAULT <默认值>;

修改表时添加默认值约束的语法：ALTER TABLE <数据表名>
CHANGE  COLUMN  <字段名>  <数据类型>  DEFAULT  <默认值>;

删除默认值约束的语法：ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL;

6.查看表的约束

语法：SHOW CREATE TABLE <数据表名>;

show create table stu_cou;

 

 注意：主表和从表的操作顺序

删除数据的时候，先删除从表，再删除主表。
添加数据的时候，先添加主表，再添加从表。
创建表的时候，先创建主表，再创建从表。
删除表的时候，先删除从表，再删除主表。