)
约束概述数据完整性是指数据的精确性和可靠性,它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的为了保证数据的完整性,SQL规范以约束的方式对表数据进行额外的条件限制,从以下四个方面考虑:实体完整性:例如,同一个表中,不能存在两条完全无法区分的记录域完整性:例如:年龄范围0-120,性别范围男/女引用完整性:例如:员工所在部门,在部门表中要能找到这个部门用户自定义完整性:例如:用户名唯一,密码不能为空等,本部门经理的工资不得高于本部门职工平均工资的5倍如何添加/删除约束CREATE TABLE时添加约束ALTER TABLE时增加约束,删除约束如何查看表中的约束SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE table_name employees;非空约束作用:限定某个字段/某列的值不允许为空关键字:NOT NULL特点:默认,所有的类型的值都可以是NULL,包括INT,FLOAT等数据类型非空约束只能出现在表对象的列上,只能某个列单独限定非空,不能组合非空一个表可以又很多列都分别限定了非空空字符串不等于NULL,0也不等于NULL添加非空约束CREATE TABLE test1( id INT NOT NULL, last_name VARCHAR(15) NOT NULL, email VARCHAR(25), salary DECIMAL(10,2) );在ALTER TABLE时添加约束ALTER TABLE test1 MODIFY email VARCHAR(25) NOT NULL;在ALTER TABLE时删除约束ALTER TABLE test1 MODIFY email VARCHAR(25) NULL;唯一性约束UNIQUE用来限制某个字段/某列的值不能重复同一个表可以有多个唯一性约束唯一约束可以是某一个列的值唯一,也可以是多个列组合的值唯一唯一性约束允许列值为空在创建唯一约束的时候,如果不给唯一约束命名,就默认和列名相同可以向声明为unique的字段上添加NULL值,而且可以多次添加NULLCREATE TABLE test2( id INT UNIQUE, #列级约束 last_name VARCHAR(15), email VARCHAR(25), salary DECIMAL(10,2), #表级约束 CONSTRAINT uk_test2_email UNIQUE(email) );在ALTER TABLE时添加约束方式1:ALTER TABLE test2 ADD CONSTRAINT uk_test2_sal UNIQUE(salary);方式2:ALTER TABLE test2 MODIFY last_name VARCHAR(15) UNIQUE;复合的唯一性约束两个字段都相同才不允许插入CREATE TABLE USER( id INT, name VARCHAR(15), password VARCHAR(25), #表级约束 CONSTRAINT uk_user_name_pwd UNIQUE(name,password) );删除唯一性约束添加唯一性约束的列上也会自动创建唯一性索引删除唯一约束之恶能通过删除唯一索引的方式删除删除时需要指定唯一索引名,唯一索引名就和唯一约束名一样如果创建唯一约束时未指定名称,如果是单列,就默认和列名相同,如果是组合列,那么默认和()中排在第一个的列名相同ALTER TABLE test2 DROP INDEX last_name;PRIMARY KEY约束作用:用来标识表中的一行记录特点:主键约束相当于唯一约束非空约束的组合,主键约束列不允许重复,也不允许出现空值.一个表最多只能有一个主键约束,建立主键约束可以在列级别创建,也可以在表级别上创建主键约束对应着表中的一列或者多列(复合主键)如果是多列组合的复合主键约束,那么这些列都不允许为空值,并且组合的值不允许重复MySQL的主键约束名总是PRIMARY,就算自己命名了主键约束名也没用当创建主键约束时,系统会在所在的列或列组合上建立对应的主键索引(能够根据主键查询的,就根据主键查询,效率更高),如果删除主键约束了,主键约束对应的索引就自动删除了.需要注意的一点是,不要修改主键字段的值,因为主键是记录数据的唯一标识,如果修改了主键的值,就有可能会破坏数据的完整性.在CREATE TABLE时添加约束CREATE TABLE test3( id INT PRIMARY KEY, last_name VARCHAR(15), salary DECIMAL(10,2), email VARCHAR(25) );在ALTER TABLE时添加约束ALTER TABLE test6 ADD PRIMARY KEY (id);删除主键约束(在实际开发中不会去删除主键约束)ALTER TABLE test6 DROP PRIMARY KEY (id);自增列:AUTO_INCREMENT作用:某个字段的值自增特点和要求:1.一个表最多只能有一个自增长列2.当需要产生唯一标识符或顺序值时,可设置自增长3.自增长列约束必须是键列(主键列,唯一键列)4.自增约束的列数据类型必须是整数类型5.如果自增列制定了0和null,会在当前最大值的基础上自增,如果自增列手动指定了具体值,直接赋值为具体值开发中,一旦主键作用的字段上声明有AUTO_INCREMENT时,我们在添加数据时不要给主键对应字段赋值在CREATE TABLE时添加CREATE TABLE test7( id INT PRIMARY KEY AUTO_INCREMENT, last_name VARCHAR(15) );在ALTER TABLE时添加ALTER TABLE test7 MODIFY id INT AUTO_INCREMENT;在ALTER TABLE时删除ALTER TABLE test7 MODIFY id INT AUTO_INCREMENT;MySQL8.0新特性---自增变量的持久化在MySQL5.7中,对于自增主键的分配规则,是由InnoDB数据字典内部一个计数器来决定的,而该计数器只在内存中维护,并不会持久化到磁盘中,当数据库重启时,该计数器会被初始化MySQL8.0将自增主键的计数器持久化到重做日志中,每次计数器发生改变,都会将其写入重做日志中,如果数据库重启,InnoDB会根据重做日志中的信息来初始化计数器的内存值FOREIGN KEY约束作用:限制某个表的某个字段的引用完整性比如:员工表的员工所在部门的选择,必须在部门表能找到对应的部分主表(父表):被引用的表,被参考的表从表(子表):引用别人的表,参考别人的表例如:员工表的员工所在部门这个字段的值要参考部门表:部门表是主表,员工表是从表从表的外键列,必须引用主表的主键或唯一约束的列在创建外键约束时,如果不给外键约束命名,默认不是列名,而是自动产生一个外键名,也可以指定外键约束名创建表时就指定外键约束的话,先创建主表,再创建从表删表时,先删从表,再删主表当主表的记录被从表参照时,主表的记录将不允许删除,如果要删除数据,需要先删除从表中依赖该记录的数据,然后才可以删除主表的数据在从表中指定外键约束,并且一个表可以建立多个外键约束从表的外键列与主表被参照列的名字可以不相同,但是数据类型必须一样,逻辑意义一致当创建外键约束时,系统默认会在所在列上建立对应的普通索引,但是索引名是列名,不是外键约束的名(根据外键查询效率很高)删除外键约束后,必须手动删除对应索引在CREATE TABLE时添加CREATE TABLE dept1( dept_id INT PRIMARY KEY, dept_name VARCHAR(15) ); CREATE TABLE emp1( emp_id INT PRIMARY KEY AUTO_INCREMENT, emp_name VARCHAR(15), department_id INT, CONSTRAINT fk_emp1_dept_id FOREIGN KEY(department_id) REFERENCES dept1(dept_id) );在ALTER TABLE时添加ALTER TABLE myemp2 ADD CONSTRAINT fk_emp2_dept_id FOREIGN KEY(department_id) REFERENCES dept2(dept_id);约束等级:Cascade方式:在父表update/delete记录时,同步update/delete掉子表的匹配记录Set null方式:在父表update/delete记录时,将子表上匹配记录设为null,但是要注意子表的外键列不能为not nullNo action方式:如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作Restrict方式:同no action,都是立即检查外键约束Set default方式:父表有变更时,子表将外键列设置成一个默认的值,但Innodb不能识别如果没有指定等级,就相当于Restrict方式对于外键约束,最好是采用 ON UPDATE CASCADE ON DELETE RESTRICT方式删除外键约束#删除外键约束 ALTER TABLE emp1 DROP FOREIGN KEY fk_emp1_dept_id; #再手动删除外键约束对应的普通索引 ALTER TABLE emp1 DROP INDEX fk_emp1_dept_id;在MySQL里,外键约束是有成本的,需要消耗系统资源,对于大并发的SQL操作,有可能会不合适,比如大型网站的中央数据库,可能会因为外键约束的系统开销而变得非常慢,所以,MySQL允许你不使用系统自带的外键约束,在应用层面完成检查数据一致性的逻辑,也就是说,即使你不用外键约束,也要想办法通过应用层面的附加逻辑,来实现外键约束的功能,确保数据的一致性阿里开发规范强制要求,不得使用外键约束与级联,一切外键概念必须在应用层解决CHECK约束作用:检查某个字段的值是否符合xx要求,一般指的是值的范围MySQL5.7可以使用check约束,但check约束对数据验证没有任何作用,添加数据时,没有任何错误或警告但是MySQL8.0中可以使用check约束了CREATE TABLE test10 ( id INT, last_name VARCHAR(15), salary DECIMAL(10,2) CHECK(salary 2000) );DEFAULT约束作用:给某个字段/某列指定默认值,一旦设置默认值,在插入数据时,如果此字段没有显式赋值,则赋值为默认值在CREATE TABLE时添加约束CREATE TABLE test11 ( id INT, last_name VARCHAR(15), salary DECIMAL(10,2) DEFAULT 2000 );在ALTER TABLE 时添加约束ALTER TABLE test12 MODIFY salary DECIMAL(8,2) DEFAULT 2500;在ALTER TABLE时删除约束ALTER TABLE test12 MODIFY salary DECIMAL(8,2);为什么建表时,加not null default 或default 0不想让表中出现Null值为什么不想要Null值1.不好比较,null是一种特殊值,比较时只能用专门的is null和is not null来比较,碰到运算符,通常返回null2.效率不高,影响提高索引效果,因此,我们往往在建表时not null default 或 default 0带AUTO_INCREMENT约束的字段值是从1开始的么MySQL中,默认AUTO_INCREMENT的初始值是1,每新增一条记录,字段值自动加1,设置自增属性的时候,还可以指定第一条插入记录的自增字段的值,这样新插入的记录的自增字段值从初始值开始递增,如在表中插入第一条记录,同时指定Id值为5,则以后插入的记录的id值就会从6开始往上增加,添加主键约束时,往往需要设置字段自动增加属性并不是每个表都可以任意选择存储引擎?外键约束不能跨引擎使用MySQL支持多种存储引擎,每个表都可以指定一个不同的存储引擎,需要注意的是,外键约束是用来保证数据的参照完整性的,如果表之间需要关联外键,却绑定了不同的存储引擎,那么这些表之间是不能创建外键约束的,所以存储引擎的选择也不完全是随意的
约束概述数据完整性是指数据的精确性和可靠性,它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的为了保证数据的完整性,SQL规范以约束的方式对表数据进行额外的条件限制,从以下四个方面考虑:实体完整性:例如,同一个表中,不能存在两条完全无法区分的记录域完整性:例如:年龄范围0-120,性别范围男/女引用完整性:例如:员工所在部门,在部门表中要能找到这个部门用户自定义完整性:例如:用户名唯一,密码不能为空等,本部门经理的工资不得高于本部门职工平均工资的5倍如何添加/删除约束CREATE TABLE时添加约束ALTER TABLE时增加约束,删除约束如何查看表中的约束SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE table_name employees;非空约束作用:限定某个字段/某列的值不允许为空关键字:NOT NULL特点:默认,所有的类型的值都可以是NULL,包括INT,FLOAT等数据类型非空约束只能出现在表对象的列上,只能某个列单独限定非空,不能组合非空一个表可以又很多列都分别限定了非空空字符串不等于NULL,0也不等于NULL添加非空约束CREATE TABLE test1( id INT NOT NULL, last_name VARCHAR(15) NOT NULL, email VARCHAR(25), salary DECIMAL(10,2) );在ALTER TABLE时添加约束ALTER TABLE test1 MODIFY email VARCHAR(25) NOT NULL;在ALTER TABLE时删除约束ALTER TABLE test1 MODIFY email VARCHAR(25) NULL;唯一性约束UNIQUE用来限制某个字段/某列的值不能重复同一个表可以有多个唯一性约束唯一约束可以是某一个列的值唯一,也可以是多个列组合的值唯一唯一性约束允许列值为空在创建唯一约束的时候,如果不给唯一约束命名,就默认和列名相同可以向声明为unique的字段上添加NULL值,而且可以多次添加NULLCREATE TABLE test2( id INT UNIQUE, #列级约束 last_name VARCHAR(15), email VARCHAR(25), salary DECIMAL(10,2), #表级约束 CONSTRAINT uk_test2_email UNIQUE(email) );在ALTER TABLE时添加约束方式1:ALTER TABLE test2 ADD CONSTRAINT uk_test2_sal UNIQUE(salary);方式2:ALTER TABLE test2 MODIFY last_name VARCHAR(15) UNIQUE;复合的唯一性约束两个字段都相同才不允许插入CREATE TABLE USER( id INT, name VARCHAR(15), password VARCHAR(25), #表级约束 CONSTRAINT uk_user_name_pwd UNIQUE(name,password) );删除唯一性约束添加唯一性约束的列上也会自动创建唯一性索引删除唯一约束之恶能通过删除唯一索引的方式删除删除时需要指定唯一索引名,唯一索引名就和唯一约束名一样如果创建唯一约束时未指定名称,如果是单列,就默认和列名相同,如果是组合列,那么默认和()中排在第一个的列名相同ALTER TABLE test2 DROP INDEX last_name;PRIMARY KEY约束作用:用来标识表中的一行记录特点:主键约束相当于唯一约束非空约束的组合,主键约束列不允许重复,也不允许出现空值.一个表最多只能有一个主键约束,建立主键约束可以在列级别创建,也可以在表级别上创建主键约束对应着表中的一列或者多列(复合主键)如果是多列组合的复合主键约束,那么这些列都不允许为空值,并且组合的值不允许重复MySQL的主键约束名总是PRIMARY,就算自己命名了主键约束名也没用当创建主键约束时,系统会在所在的列或列组合上建立对应的主键索引(能够根据主键查询的,就根据主键查询,效率更高),如果删除主键约束了,主键约束对应的索引就自动删除了.需要注意的一点是,不要修改主键字段的值,因为主键是记录数据的唯一标识,如果修改了主键的值,就有可能会破坏数据的完整性.在CREATE TABLE时添加约束CREATE TABLE test3( id INT PRIMARY KEY, last_name VARCHAR(15), salary DECIMAL(10,2), email VARCHAR(25) );在ALTER TABLE时添加约束ALTER TABLE test6 ADD PRIMARY KEY (id);删除主键约束(在实际开发中不会去删除主键约束)ALTER TABLE test6 DROP PRIMARY KEY (id);自增列:AUTO_INCREMENT作用:某个字段的值自增特点和要求:1.一个表最多只能有一个自增长列2.当需要产生唯一标识符或顺序值时,可设置自增长3.自增长列约束必须是键列(主键列,唯一键列)4.自增约束的列数据类型必须是整数类型5.如果自增列制定了0和null,会在当前最大值的基础上自增,如果自增列手动指定了具体值,直接赋值为具体值开发中,一旦主键作用的字段上声明有AUTO_INCREMENT时,我们在添加数据时不要给主键对应字段赋值在CREATE TABLE时添加CREATE TABLE test7( id INT PRIMARY KEY AUTO_INCREMENT, last_name VARCHAR(15) );在ALTER TABLE时添加ALTER TABLE test7 MODIFY id INT AUTO_INCREMENT;在ALTER TABLE时删除ALTER TABLE test7 MODIFY id INT AUTO_INCREMENT;MySQL8.0新特性---自增变量的持久化在MySQL5.7中,对于自增主键的分配规则,是由InnoDB数据字典内部一个计数器来决定的,而该计数器只在内存中维护,并不会持久化到磁盘中,当数据库重启时,该计数器会被初始化MySQL8.0将自增主键的计数器持久化到重做日志中,每次计数器发生改变,都会将其写入重做日志中,如果数据库重启,InnoDB会根据重做日志中的信息来初始化计数器的内存值FOREIGN KEY约束作用:限制某个表的某个字段的引用完整性比如:员工表的员工所在部门的选择,必须在部门表能找到对应的部分主表(父表):被引用的表,被参考的表从表(子表):引用别人的表,参考别人的表例如:员工表的员工所在部门这个字段的值要参考部门表:部门表是主表,员工表是从表从表的外键列,必须引用主表的主键或唯一约束的列在创建外键约束时,如果不给外键约束命名,默认不是列名,而是自动产生一个外键名,也可以指定外键约束名创建表时就指定外键约束的话,先创建主表,再创建从表删表时,先删从表,再删主表当主表的记录被从表参照时,主表的记录将不允许删除,如果要删除数据,需要先删除从表中依赖该记录的数据,然后才可以删除主表的数据在从表中指定外键约束,并且一个表可以建立多个外键约束从表的外键列与主表被参照列的名字可以不相同,但是数据类型必须一样,逻辑意义一致当创建外键约束时,系统默认会在所在列上建立对应的普通索引,但是索引名是列名,不是外键约束的名(根据外键查询效率很高)删除外键约束后,必须手动删除对应索引在CREATE TABLE时添加CREATE TABLE dept1( dept_id INT PRIMARY KEY, dept_name VARCHAR(15) ); CREATE TABLE emp1( emp_id INT PRIMARY KEY AUTO_INCREMENT, emp_name VARCHAR(15), department_id INT, CONSTRAINT fk_emp1_dept_id FOREIGN KEY(department_id) REFERENCES dept1(dept_id) );在ALTER TABLE时添加ALTER TABLE myemp2 ADD CONSTRAINT fk_emp2_dept_id FOREIGN KEY(department_id) REFERENCES dept2(dept_id);约束等级:Cascade方式:在父表update/delete记录时,同步update/delete掉子表的匹配记录Set null方式:在父表update/delete记录时,将子表上匹配记录设为null,但是要注意子表的外键列不能为not nullNo action方式:如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作Restrict方式:同no action,都是立即检查外键约束Set default方式:父表有变更时,子表将外键列设置成一个默认的值,但Innodb不能识别如果没有指定等级,就相当于Restrict方式对于外键约束,最好是采用 ON UPDATE CASCADE ON DELETE RESTRICT方式删除外键约束#删除外键约束 ALTER TABLE emp1 DROP FOREIGN KEY fk_emp1_dept_id; #再手动删除外键约束对应的普通索引 ALTER TABLE emp1 DROP INDEX fk_emp1_dept_id;在MySQL里,外键约束是有成本的,需要消耗系统资源,对于大并发的SQL操作,有可能会不合适,比如大型网站的中央数据库,可能会因为外键约束的系统开销而变得非常慢,所以,MySQL允许你不使用系统自带的外键约束,在应用层面完成检查数据一致性的逻辑,也就是说,即使你不用外键约束,也要想办法通过应用层面的附加逻辑,来实现外键约束的功能,确保数据的一致性阿里开发规范强制要求,不得使用外键约束与级联,一切外键概念必须在应用层解决CHECK约束作用:检查某个字段的值是否符合xx要求,一般指的是值的范围MySQL5.7可以使用check约束,但check约束对数据验证没有任何作用,添加数据时,没有任何错误或警告但是MySQL8.0中可以使用check约束了CREATE TABLE test10 ( id INT, last_name VARCHAR(15), salary DECIMAL(10,2) CHECK(salary 2000) );DEFAULT约束作用:给某个字段/某列指定默认值,一旦设置默认值,在插入数据时,如果此字段没有显式赋值,则赋值为默认值在CREATE TABLE时添加约束CREATE TABLE test11 ( id INT, last_name VARCHAR(15), salary DECIMAL(10,2) DEFAULT 2000 );在ALTER TABLE 时添加约束ALTER TABLE test12 MODIFY salary DECIMAL(8,2) DEFAULT 2500;在ALTER TABLE时删除约束ALTER TABLE test12 MODIFY salary DECIMAL(8,2);为什么建表时,加not null default 或default 0不想让表中出现Null值为什么不想要Null值1.不好比较,null是一种特殊值,比较时只能用专门的is null和is not null来比较,碰到运算符,通常返回null2.效率不高,影响提高索引效果,因此,我们往往在建表时not null default 或 default 0带AUTO_INCREMENT约束的字段值是从1开始的么MySQL中,默认AUTO_INCREMENT的初始值是1,每新增一条记录,字段值自动加1,设置自增属性的时候,还可以指定第一条插入记录的自增字段的值,这样新插入的记录的自增字段值从初始值开始递增,如在表中插入第一条记录,同时指定Id值为5,则以后插入的记录的id值就会从6开始往上增加,添加主键约束时,往往需要设置字段自动增加属性并不是每个表都可以任意选择存储引擎?外键约束不能跨引擎使用MySQL支持多种存储引擎,每个表都可以指定一个不同的存储引擎,需要注意的是,外键约束是用来保证数据的参照完整性的,如果表之间需要关联外键,却绑定了不同的存储引擎,那么这些表之间是不能创建外键约束的,所以存储引擎的选择也不完全是随意的
