Phase2 Day18 SQL 约束

约束

• 当我们创建数据表的时候，我们需要对它的字段进行一些约束，目的在于保证数据的准确性和一致性
• 常见的约束有以下几种：主键约束，外键约束，唯一性约束，非空约束，DEFAULT约束，以及CHECK约束

PRIMARY KEY 主键

• 主键的作用是唯一标识一条记录。所以它不能重复，也不能为空，我们可以认为它是唯一性约束和非空 约束的组合。
• 一张数据表的主键最多只能有一个 (推荐每张表都设置一个主键)。
• 主键可以是一个字段， 也可以由多个字段符合组成

use mydb2;
# primary key
#1. 一个字段 
create table t_student (
	id int primary key,//定义时指定
    name varchar(255),
    age int
);
show keys from t_student;
drop table t_student;
create table t_student (
	id int,
    name varchar(255),
    age int,
    primary key(id)//后面指定
);

#2. 多个字段 
create table t_scrore(
	sid int,
    cid int,
    score int,
    primary key(sid, cid)
);
show index from t_scrore;

# 创建表之后，再指定主键 (不推荐)
drop table t_scrore;
create table t_score(
	sid int,
    cid int,
    score int
);
show index from t_score;
alter table t_score add primary key(sid, cid);


# auto_increment 自动添加主键
1. auto_increment 只能作用于被 primary key 或者是 unique 修饰的字段。 
2. auto_increment 作用字段的类型必须是数值类型。
3. 一张数据表只能有一个自增长字段。

drop table t_student;
create tab	le t_student (
	id int primary key auto_increment,
    name varchar(20)
);
insert into t_student values (null, '张三'), (null, '李四');
select * from t_student;

drop table t_tmp;
create table t_tmp (
	a float(5, 2) unique auto_increment
); 

FOREIGN KEY 外键

• 外键约束的作用是确保表与表之间参照完整性。一张表的外键往往对应另一张表的主键。外键可以是重复的，也可以为空
• 添加外键之后，对两张表都会有一些约束。具体体现在：

1. t_student 表不能随意的添加和修改。
2. t_class 表不能随意的删除和修改。

# 外键 

drop table t_student;
create table t_student(
	id int primary key auto_increment,
    name varchar(255),
    cid int,
    foreign key(cid) references t_class(id)
);

create table t_student(
	id int primary key auto_increment,
    name varchar(255),
    cid int,
    constraint fk_cid foreign key(cid) references t_class(id)//给外键添加名字
);
show index from t_student;

# 当然我们还可以在创建表之后，再添加外键 (不推荐)
create table t_student(
	id int primary key auto_increment,
    name varchar(255),
    cid int
);
show index from t_student;
alter table t_student add constraint fk_cid foreign key(cid) references t_class(id);

create table t_class(
	id int primary key auto_increment,
    name varchar(255) 
);

//t_class 表中被引用的键不能随意的删除和修改
insert into t_class (name) values ('一班'), ('二班');
delete from t_class where id = 1;//无法删除
update t_class set id = 100 where id = 1;//无法修改
update t_class set id = 100 where id = 2;//可以修改没被引用的
delete from t_class where id = 100;//可以删除

select * from t_class;

//t_student 表不能随意的添加和修改。
insert into t_student(name, cid) values('张三', 100);//无法添加
insert into t_student(name, cid) values('张三', 1);//可以添加
update t_student set cid=100 where name='张三';//无法修改

select * from t_student;

# 需要删除student中所有的class引用才能删除class
update t_student set cid = null where cid = 1;
delete from t_class where id = 1;

级联删除

• 删除 t_class 表中的记录，那么 t_student 中引用相应记录的行也会一起被删除

drop table t_student;
create table t_student (
	id int primary key auto_increment,
    name varchar(20) not null,
    cid int,
    constraint fk_cid foreign key(cid) references t_class(id) on delete cascade
);

select * from t_class;
insert into t_class values(1, '一班'), (2, '二班');
insert into t_student(name, cid) values('张三', 1);

delete from t_class where id = 1;//可以删除，且student中数据也被删除
select * from t_student;

级联置空

• 删除 t_class 表中的记录，那么 t_student 中引用相应记录的行的外键会被置为 null

# 级联置空 
drop table t_student;
create table t_student (
	id int primary key auto_increment,
    name varchar(20) not null,
    cid int,
    constraint fk_cid foreign key(cid) references t_class(id) on delete set null
);
select * from t_student;
insert into t_student(name, cid) values('张三', 2);

delete from t_class where id = 2;
select * from t_student;

外键的缺点

外键虽然可以保证表与表之间的参照完整性，但是它的缺点也很明显。

1. 影响数据库的性能
2. 在高并发场景中容易引起死锁
3. 当数据量很大的时候，为了保证查询的性能，我们需要进行分库分表。一旦分库分表，我们就不能
   保证参照的完整性了

正是因为这些原因，所以《阿里巴巴开发手册》中规定：不要在数据库中设置外键，一切的参照完整都应该在业务层中完成
当然，这并不是说，外键就一无是处。如果参照完整性都在业务层中完成，也会导致一些问题。

1. 业务层与数据耦合了。
2. 增加了业务层的逻辑。
3. 不能够在数据库的层面保证表之间的参照完整性。

所以，我们应该正确地看待外键。在以下场景中，我们是可以使用外键的。

• 并发度不高
• 数据量不大，不需要分库分表。
• 正确性 > 性能

唯一性约束unique

• 唯一性约束保证了字段的值是唯一的。即使我们有了主键，我们还是可以对其它字段设置唯一性约束。
• 注意事项： null 与 null 是不相同的，所以唯一性约束的字段，可以有多个 null 值

唯一性约束 unique

create table t_unique (
	id int primary key auto_increment,
    a int unique,
    b varchar(255) unique
);
# insert into t_unique(a,b) values (1, 2);
# insert into t_unique(a,b) values (2, 3);
# insert into t_unique(a,b) values (3, 3);
insert into t_unique(a) values(null);
insert into t_unique(a) values(null);
select * from t_unique;

非空形约束

• 非空型约束保证了字段的值不为 null ，必须有个具体的值

# 非空性约束 not null
create table t_notnull (
	id int primary key auto_increment,
    name varchar(255) not null
);

insert into t_notnull (id) values (1);
insert into t_notnull (id, name) values (1, '张三');
select * from t_notnull;
update t_notnull set name='李四' where id = 1;

DEFAULT

• DEFAULT 表示字段的默认值。如果插入数据的时候，没有给该字段取值，就会设置为默认值

# default 
create table t_default (
	id int primary key auto_increment,
    name varchar(255),
    age int not null default 18
);

insert into t_default(id, name) values (null, '张三');
select * from t_default;

CHECK

• CHECK 表示自定义约束。MySQL 没有实现这个功能，但是其它商用型数据库，比如 Oracle 是有这个功能的

# check 
create table t_check(
	height float(3, 2) check(height between 0.00 and 3.00)
);
insert into t_check values (1.80);
# MySQL没有实现这个功能
insert into t_check values (3.80);
select * from t_check;