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;
注意:主表和从表的操作顺序
删除数据的时候,先删除从表,再删除主表。
添加数据的时候,先添加主表,再添加从表。
创建表的时候,先创建主表,再创建从表。
删除表的时候,先删除从表,再删除主表。