mysql删除check约束_高级SQL特性——约束与索引

一、约束

1.约束的作用

对表中数据进行进一步限制，保证表中数据的完整性、一致性和正确性。

2.约束的分类

1）主键约束

主键能够唯一地标识表中的一行，比如学生表中的学生id。

它的主要作用就是能够将表中的每一行数据区分开来，这样可以避免在执行update或delete操作时发生错误。

满足以下条件的任意列都可以作为主键：

• 任意两行的主键值不相同
• 每一行都存在主键值（即主键值不能为NULL）
• 包含主键值的列从不进行修改与更新

设置主键有下面几种方式：

• 在表定义时直接指定主键
• 使用ALTER TABLE语句来设置主键约束

--第一种方式
create table student(
    stu_id char(9) primary key,
    name char(10));

--第二种方式
create table student(
    stu_id char(9),
    name char(10),
    primary key(stu_id)
);

--第三种方式
alter table student add constraint primary key(stu_id);

如果要更新主键，需要先将表中原来的主键drop掉，在重新添加主键约束。

2）外键约束

外键是表中的一列，它的值必须在另一个表的主键中，外键是保证引用完整性的极其重要部分。

举个栗子：

订单表（订单号，订单日期，顾客id）

顾客表（顾客id，顾客name，顾客地址）

订单表中，订单号是唯一的，但是顾客id不一定是唯一的，因为同一个顾客可能会产生多个订单。而在顾客表中，顾客id是唯一的。

订单表中顾客id的合法值应该为顾客表中顾客id的某个值，所以我们定义订单表中的顾客id为外键，它的值要以顾客表的主键（顾客id）为参考。

创建外键约束的方法：

create table Orders(
    order_num int not null primary key,
    order_date datetime not null,
    cust_id char(10) not null references Customers(cust_id)
);

--第二种方法
alter table Orders add constraint foreign key(cust_id) references Customers(cust_id);

最后再说一下什么叫引用完整性。

引用完整性就是定义外关键字与主关键字之间的引用规则。如果要删除被引用的对象，那么也要删除引用它的所有对象，或者把引用值设置为空(如果允许的话)。

订单表中的顾客id为外键，它引用的是顾客表的主键。所以订单表为引用对象，顾客表为被引用对象。设想如果我们要从顾客表中删除某个顾客，而该顾客在订单表中有引用记录，一旦将该顾客删除，那么对应的订单表将失去引用对象，这对系统来说确实是不安全的。

3）唯一约束

唯一性约束用来保证某列中的数值是唯一的。它和主键的区别有下面几点：

• 主键必须非空，而唯一约束可包含NULL
• 每一个表只能有一个主键，却可以有多个唯一约束
• 唯一约束可修改或更新
• 唯一约束不能用来定义外键

创建方式：

• 直接在表定义时使用unique关键字
• 使用单独的constraint定义

4）check约束

check约束用来保证某一列的数值满足某种特定的条件，它的用途主要有以下几种：

• 检查最大最小值，比如订单数量不能为0
• 指定范围：比如发货日期必须大于等于今天的日期并且不超过明年的这个时候
• 只允许特定的值，比如性别列中只允许填写"M" or "F"

创建check约束：

---第一种方法
create table OrderItem(
    order_num int not null,
    order_item int not null,
    quantity int not null check (quantity > 0)
);

---第二种方法
add constraint check (gender in ['M','F']);

不够mysql中check约束貌似没用？？？

使用enum()和触发器来解决~~~

5）非空和默认值

这也是常用的约束手段，分别使用not null和default来进行约束即可。

二、索引

1.索引的作用

加快搜索速度。

比如说你需要在一本书中寻找一个术语的位置，如果不使用索引，你需要从从第一页一行一行的找，虽然也能找到但是效率太低。如果书中有索引，且索引中有这个术语和所在的位置，我们就能快速找到它的位置了。

2.索引的使用场景

数据库中表的数据量较大的情况下，对于查询响应时间不能满足业务需求，可以合理的使用索引提升查询效率。

3.索引的分类

• 普通索引（单列）
• 复合索引（多列）
• 唯一索引
• 主键索引
• 全文索引

# 建立普通索引
create index <索引名> on <表名>(列名)；

# 建立复合索引
create index <索引名> on <表名>(列名组)；

# 创建唯一索引
create unique index <索引名> on <表名>(列名)；

# 唯一索引
alter table <表名> add primary key(列名);

/* 全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较
目前只有char、varchar，text 列上可以创建全文索引 */

# 删除索引
drop index <索引名> on <表名>

• 索引名必须唯一
• 主键索引是一种特殊的唯一索引，它的值是唯一的且不能存在空值，通常在表定义时直接设置

唯一约束和唯一索引在 MySQL 数据库里区别：

1. 概念上不同，约束是为了保证数据的完整性，索引是为了辅助查询
2. 创建唯一约束时，会自动的创建唯一索引
3. 在理论上，不一样，在实际使用时，基本没有区别

4.索引的优点

• 为用来排序或者是分组的字段添加索引可以加快分组和排序顺序
• 加快了搜索的速度
• 建立索引可以加快表与表之间的连接

5.索引的缺点

• 索引的确能改善搜索的速度，但是却降低了更新、删除数据的性能，原因在于每次删除或更新数据就需要对索引进行更新
• 索引其实也是一张表，所以建立索引必然会占据一定的内存空间