文章目录
1. 约束概述
在 MySQL 中,约束是指对表中数据的一种约束,能够帮助数据库管理员更好地管理数据库,并且能够确保数据库中数据的正确性和有效性。
约束的分类:
约束 描述 关键字 非空约束 限制该字段的数据不能为null NOT NULL 唯一约束 保证该字段的所有数据都是唯一、不重复的 UNIQUE 主键约束 主键是一行数据的唯一标识,要求非空且唯一 PRIMARY KEY 默认约束 保存数据时,如果未指定该字段的值,则采用默认值 DEFAULT 检查约束(8.0.16版本 之后) 保证字段值满足某一个条件 CHECK 外键约束 用来让两张表的数据之间建立连接,保证数据的一致 性和完整性 FOREIGN KEY 注意事项:
约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束。
以上 6 种约束中,一个数据表中只能有一个主键约束,其它约束可以有多个。
查看表中的约束:
SHOW CREATE TABLE <数据表名>;
2. 主键约束(PRIMARY KEY)
主键(PRIMARY KEY)的完整称呼是“主键约束”,是 MySQL中使用最为频繁的约束。一般情况下,为了便于 DBMS 更快的查找到表中的记录,都会在表中设置一个主键。
注意事项:
主键约束的特性是:NOT NULL、UNIQUE,也就是说主键约束包含了非空约束和唯一约束。
通常通过给字段添加 AUTO_INCREMENT 属性来实现主键自增长,AUTO_INCREMENT 约束的字段只能是整数类型(TINYINT、SMALLINT、INT、BIGINT 等)。
修改auto_increment的SQL语句:
alter table tablename auto_increment=NUMBER;
添加主键约束:
创建表时添加主键约束:
#添加单字段主键:<字段名> <数据类型> PRIMARY KEY [默认值] CREATE TABLE tb_emp3 (id INT(11) PRIMARY KEY AUTO_INCREMENT,name VARCHAR(25)); #添加单子段主键:[CONSTRAINT <约束名>] PRIMARY KEY [字段名] CREATE TABLE tb_emp4(id INT(11),name VARCHAR(25),PRIMARY KEY(id))AUTO_INCREMENT=100; #添加多字段主键:PRIMARY KEY [字段1,字段2,…,字段n] CREATE TABLE tb_emp5(name VARCHAR(25),deptId INT(11),PRIMARY KEY(name,deptId));
修改表时添加主键约束:
# 添加单子段主键:ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>); ALTER TABLE tb_emp2 ADD PRIMARY KEY(id);
删除主键约束:由于主键约束在一个表中只能有一个,因此不需要指定主键名就可以删除一个表中的主键约束。
ALTER TABLE <数据表名> DROP PRIMARY KEY;
3. 外键约束(FOREIGN KEY)
MySQL外键约束(FOREIGN KEY)是表的一个特殊字段,经常与主键约束一起使用。对于两个具有关联关系的表而言,相关联字段中主键所在的表就是主表(父表),外键所在的表就是从表(子表)。外键用来建立主表与从表的关联关系,为两个表的数据建立连接,约束两个表中数据的一致性和完整性。比如,一个水果摊,只有苹果、桃子、李子、西瓜等 4 种水果,那么,你来到水果摊要买水果就只能选择苹果、桃子、李子和西瓜,其它的水果都是不能购买的。
注意事项:
主表删除某条记录时,从表中与之对应的记录也必须有相应的改变。一个表可以有一个或多个外键,外键可以为空值,若不为空值,则每一个外键的值必须等于主表中主键的某个值。
主表必须已经存在于数据库中,或者是当前正在创建的表。如果是后一种情况,则主表与从表是同一个表,这样的表称为自参照表,这种结构称为自参照完整性。
外键中列的数目、数据类型必须和主表的主键中列的数目、数据类型相同。
添加外键约束:
创建表时添加外键约束:
# [CONSTRAINT <外键名>] FOREIGN KEY 字段名 [,字段名2,…] REFERENCES <主表名> 主键列1 [,主键列2,…] CREATE TABLE tb_emp6(id INT(11) PRIMARY KEY,name VARCHAR(25),deptId INT(11),salary FLOAT, FOREIGN KEY(deptId) REFERENCES tb_dept1(id));
修改表时添加外键约束:
# ALTER TABLE <数据表名> ADD CONSTRAINT <外键名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>); ALTER TABLE tb_emp2 ADD CONSTRAINT fk_tb_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id);
删除外键约束:
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;
删除更行行为:添加了外键之后,再删除父表数据时产生的约束行为,我们就称为删除/更新行为。具体的删除/更新行
为有以下几种:行为 说明 NO ACTION 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不 允许删除/更新。 (与 RESTRICT 一致) 默认行为 RESTRICT 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不 允许删除/更新。 (与 NO ACTION 一致) 默认行为 CASCADE 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则 也删除/更新外键在子表中的记录。 SET NULL 当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表 中该外键值为null(这就要求该外键允许取null)。 SET DEFAULT 父表有变更时,子表将外键列设置成一个默认的值 (Innodb不支持) ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY 主表名 (主表字段名) ON UPDATE 行为 ON DELETE 行为
4. 唯一约束(UNIQUE KEY)
- MySQL 唯一约束(Unique Key)是指所有记录中字段的值不能重复出现,唯一约束在一个表中可有多个,并且设置唯一约束的列允许有空值。
添加唯一约束:
创建表时添加唯一约束:
# <字段名> <数据类型> UNIQUE mysql> CREATE TABLE tb_dept2(id INT(11) PRIMARY KEY,name VARCHAR(22) UNIQUE);
修改表时添加唯一约束:
# ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>); ALTER TABLE tb_dept1 ADD CONSTRAINT unique_name UNIQUE(name);
删除唯一约束:
# ALTER TABLE <表名> DROP INDEX <唯一约束名>; ALTER TABLE tb_dept1 DROP INDEX unique_name;
5. 默认约束(DEFAULT)
- 默认值(Default)的完整称呼是“默认值约束(Default Constraint)”,用来指定某列的默认值。在表中插入一条新记录时,如果没有为某个字段赋值,系统就会自动为这个字段插入默认值。
添加默认约束:
创建表时添加默认约束:
# <字段名> <数据类型> DEFAULT <默认值>; CREATE TABLE tb_dept3(id INT(11) PRIMARY KEY,name VARCHAR(22), location VARCHAR(50) DEFAULT'Beijing');
修改表时添加默认约束:
# ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>; ALTER TABLE tb_dept3 CHANGE COLUMN location location VARCHAR(50) DEFAULT 'Shanghai';
删除默认约束:
# ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL; ALTER TABLE tb_dept3 CHANGE COLUMN location location VARCHAR(50) DEFAULT NULL;
6. 非空约束(NOT NULL)
- MySQL非空约束(NOT NULL)指字段的值不能为空。对于使用了非空约束的字段,如果用户在添加数据时没有指定值,数据库系统就会报错。
添加非空约束:
创建表时添加非空约束:
# <字段名> <数据类型> NOT NULL CREATE TABLE tb_dept4(id INT(11) PRIMARY KEY,name VARCHAR(22) NOT NULL);
修改表时添加非空约束:
# ALTER TABLE <数据表名>CHANGE COLUMN <字段名><字段名> <数据类型> NOT NULL; ALTER TABLE tb_dept4 CHANGE COLUMN location location VARCHAR(50) NOT NULL;
删除非空约束:
# ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NULL; ALTER TABLE tb_dept4 CHANGE COLUMN location location VARCHAR(50) NULL;
7. 检查约束(CHECK)
- MySQL检查约束(CHECK)是用来检查数据表中字段值有效性的一种手段,可以通过 CREATE TABLE 或 ALTER TABLE 语句实现。设置检查约束时要根据实际情况进行设置,这样能够减少无效数据的输入。
添加检查约束:
创建表时添加检查约束:
# CHECK <表达式> CREATE TABLE tb_emp7(id INT(11) PRIMARY KEY,salary FLOAT,CHECK(salary>0 AND salary<100),);
修改表时添加检查约束:
# ALTER TABLE tb_emp7 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>) ALTER TABLE tb_emp7 ADD CONSTRAINT check_id CHECK(id>0);
删除检查约束:
# ALTER TABLE <数据表名> DROP CONSTRAINT <检查约束名>; ALTER TABLE tb_emp7 DROP CONSTRAINT check_id;