删除逐渐约束mysql mysql删除约束check

常用约束总结下篇

• 1. 自增列AUTO_INCREMENT

• 1.1 自增列概述
• 1.2 指定自增约束
• 1.3 删除自增约束
• 1.4 MySQL 8.0新特性—自增变量的持久化

• 2. 外键约束FOREIGN KEY

• 2.1 外键约束概述
• 2.2 添加外键约束
• 2.3 删除外键约束

• 3. CHECK 约束

• 3.1 CHECK 约束概述
• 3.2 约束使用

• 4. DEFAULT约束

• 4.1 DEFAULT约束概述
• 3.2 字段加默认值
• 4.3 删除默认值约束

1. 自增列AUTO_INCREMENT

1.1 自增列概述

（1）作用

    某个字段的值自增

（2）关键字
    auto_increment

（3）特点

（1）一个表最多只能有一个自增长列
（2）当需要产生唯一标识符或顺序值时，可设置自增长
（3）自增长列约束的列必须是键列（主键列，唯一键列）
（4）自增约束的列的数据类型必须是整数类型
（5）如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

1.2 指定自增约束

（1）建表时

使用格式：

对逐渐添加自增约束

create table 表名称( 
字段名 数据类型 primary key auto_increment, 
字段名 数据类型 unique key not null, 
字段名 数据类型 unique key, 
字段名 数据类型 not null default 默认值,
 );

对唯一性约束添加自增

create table 表名称( 
字段名 数据类型 default 默认值 , 
字段名 数据类型 unique key auto_increment, 
字段名 数据类型 not null default 默认值,
primary key(字段名)
 );

使用样例：

CREATE TABLE test7(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(15)
);

INSERT INTO test7
VALUES
(1, 'Tom');

INSERT INTO test7(id,name)
VALUES
(2,'Jerry');

INSERT INTO test7(id,name)
VALUES
(3,'Mark');

INSERT INTO test7(id,name)
VALUES
(0,'Mark');

INSERT INTO test7(id,name)
VALUES
(NUll,'Mark2');

INSERT INTO test7(name)
VALUES
('Jerry');

在这里，根据日志中记录的数值实现自增

（2）建表后

alter table 表名称 modify 字段名 数据类型 auto_increment;

1.3 删除自增约束

使用格式：

alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除

使用样例：

create table employee( 
eid int primary key , 
ename varchar(20) 
);
alter table employee modify eid int auto_increment;

删除自增

ALTER TABLE test7
MODIFY id INT;

1.4 MySQL 8.0新特性—自增变量的持久化

MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。

实现实例：

CREATE TABLE test1(
 id INT PRIMARY KEY AUTO_INCREMENT 
 );

INSERT INTO test1 
VALUES
(0),(0),(0),(0);

删除id为4的记录

DELETE FROM test1 WHERE id = 4;

再添加一个数据

INSERT INTO test1 VALUES(0);

重启MySQL

再次插入一个空值

INSERT INTO test1 VALUES(0);

查询结果

没有持久化。 在MySQL 5.7系统中，对于自增主键的分配规则，是由InnoDB数据字典内部一个 计数器内存中维护 ，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化。在MySQL 8.0版本中，上述测试步骤最后一步的结果如下：

重做日志 中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。

2. 外键约束FOREIGN KEY

2.1 外键约束概述

（1）作用

    限定某个表的某个字段的引用完整性。
比如：员工表的员工所在部门的选择，必须在部门表能找到对应的部分。

（2）关键字
    FOREIGN KEY

（3）主表和从表/父表和子表

主表（父表）：被引用的表，被参考的表
从表（子表）：引用别人的表，参考别人的表
例如：员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表。
例如：学生表、课程表、选课表：选课表的学生和课程要分别参考学生表和课程表，学生表和课程表是主表，选课表是从表。

（4）特点

（1）从表的外键列，必须引用/参考主表的主键或唯一约束的列为什么？
        因为被依赖/被参考的值必须是唯一的
（2）创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表
（3）删表时，先删从表（或先删除外键约束），再删除主表
（4）当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
（5）在“从表”中指定外键约束，并且一个表可以建立多个外键约束
（6）从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。
（7）当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。（根据外键查询效率很高）
（8）删除外键约束后，必须 手动 删除对应的索引

2.2 添加外键约束

（1）建表时

使用格式：
主表

create table 主表名称( 
字段1 数据类型 primary key, 
字段2 数据类型 
);

从表

create table 从表名称( 
字段1 数据类型 primary key, 
字段2 数据类型,
 [CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)
  );

使用样例：

CREATE TABLE dept1(
dept_id INT PRIMARY KEY,
dept_name VARCHAR(20)
);

CREATE TABLE emp1(
emp_id INT,
emp_name VARCHAR(20),
department_id INT,
#表级约束方式添加外键
CONSTRAINT fk_emp1_departmen_id FOREIGN KEY(department_id) REFERENCES dept1(dept_id)
);

（2）alter table 时添加外键约束

    一般情况下，表与表的关联都是提前设计好了的，因此，会在创建表的时候就把外键约束定义好。不过，如果需要修改表的设计（比如添加新的字段，增加新的关联关系），但没有预先定义外键约束，那么，就要用修改表的方式来补充定义。

CREATE TABLE dept2(
dept_id INT PRIMARY KEY,
dept_name VARCHAR(20)
);

CREATE TABLE emp2(
emp_id INT,
emp_name VARCHAR(20),
department_id INT
);

ALTER TABLE emp2
ADD CONSTRAINT fk_emp2_department FOREIGN KEY(department_id) REFERENCES dept2(dept_id);

两个格式大致是一样的
总结：

①约束关系是针对双方的
②添加了外键约束后，主表的修改和删除数据受约束
③添加了外键约束后，从表的添加和修改数据受约束
④在从表上建立外键，要求主表必须存在
⑤删除主表时，要求从表从表先删除，或将从表中外键引用该主表的关系先删除

2.3 删除外键约束

方式一：
(1)第一步先查看约束名和删除外键约束

SELECT * FROM information_schema.table_constraints
  WHERE table_name = '表名称';

查看某个 表的约束名

ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;

方式二：
（2）第二步查看索引名和删除索引。（注意，只能手动删除）

SHOW INDEX FROM 表名称; #查看某个表的索引名

ALTER TABLE 从表名 DROP INDEX 索引名;

3. CHECK 约束

3.1 CHECK 约束概述

（1）作用

    检查某个字段的值是否符号xx要求，一般指的是值的范围

（2）关键字
CHECK

3.2 约束使用

CREATE TABLE temp( id INT AUTO_INCREMENT, 
NAME VARCHAR(20), 
age INT CHECK(age > 20), 
PRIMARY KEY(id) 
);

插入个内容要符合CHECK约束的条件

4. DEFAULT约束

4.1 DEFAULT约束概述

（1）作用

    给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

（2）关键字
DEFAULT

3.2 字段加默认值

（1）建表时

添加格式：

create table 表名称( 
字段名 数据类型 primary key, 
字段名 数据类型 unique key not null, 
字段名 数据类型 unique key, 
字段名 数据类型 not null default 默认值,
 );

使用样例：

create table employees( 
eid int primary key, 
ename varchar(20) not null, 
gender char default '男', 
tel char(11) not null default '' #默认是空字符串 
);

（2）建表后

alter table 表名称 modify 字段名 数据类型 default 默认值;

alter table 表名称 modify 字段名 数据类型 default 默认值 not null;

4.3 删除默认值约束

alter table 表名称 modify 字段名 数据类型 ;#删除默认值约束，也不保留非空约束 

alter table 表名称 modify 字段名 数据类型 not null; #删除默认值约束，保留非空约束

alter table employee modify gender char; #删除gender字段默认值约束，如果有非空约束，也一并删除 
alter table employee modify tel char(11) not null;#删除tel字段默认值约束，保留非空约束