当我们创建数据表的时候,我们需要对它的字段进行一些约束,目的在于保证数据的准确性和一致性。
常见的约束有以下几种:主键约束,外键约束,唯一性约束,非空约束,DEFAULT约束,以及CHECK约束。
一、PRIMARY KEY
主键的作用是唯一标识一条记录。所以它不能重复,也不能为空,我们可以认为它是唯一性约束和非空约束的组合。一张数据表的主键最多只能有一个 (推荐每张表都设置一个主键)。主键可以是一个字段,也可以由多个字段符合组成。
1.一个字段
# 1. primary key
# 单个字段
create database constraint_db;
use constraint_db;
# 方式一
create table t_student (
id int primary key,
name varchar(255)
);
desc t_student;
show index from t_student;#可以查看索引,索引就是主键,主键就是索引
drop table t_student;
# 方式二
create table t_student (
id int,
name varchar(255),
primary key(id)
);
show index from t_student;
2.多个字段
# 多个字段
create table t_score(
cid int,
sid int,
score int,
primary key(cid, sid)#联合主键
);
show index from t_score;
3.创建表之后,再指定主键 (不推荐)
# 创建表之后再指定主键(不推荐)
drop table t_score;
create table t_score(
cid int,
sid int,
score int
);
show index from t_score;
alter table t_score add primary key(cid, sid);
show index from t_score;
4.auto_increment关键字
auto_increment 关键字往往是和 primary key 一起使用的。它的作用是:被修饰的字段会自动增长。
# auto_increment 自增长,往往和主键一起使用
create table t_inc(
id int primary key,
name varchar(255)
);
insert into t_inc values (null, 'zs');
insert into t_inc values (1, 'zs');
insert into t_inc values (1, 'zs');
drop table t_inc;
create table t_inc(
id int primary key auto_increment,
name varchar(255)
);
insert into t_inc values (null, 'zs');
insert into t_inc values (5, 'zs');# 添加自增长后,不推荐指定主键进行赋值
insert into t_inc values (null, 'zs');# 会在最大的主键值上进行自增长
select * from t_inc;
它有以下几点需要大家注意一下:
- auto_increment 只能作用于被 primary key 或者是 unique 修饰的字段。
- auto_increment 作用字段的类型必须是数值类型。
- 一张数据表只能有一个自增长字段。
二、FOREIGN KEY
外键约束的作用是确保表与表之间参照完整性。一张表的外键往往对应另一张表的主键或者唯一键。外键可以是重复的,也可以为空。
# 2. foreign key
create table t_class (
id int primary key auto_increment,
name varchar(255),
teacher varchar(255)
);
insert into t_class(name,teacher) values('一班','赵一');
insert into t_class(name,teacher) values('二班','孙二');
insert into t_class(name,teacher) values('三班','张三');
select * from t_class;
drop table t_student;
create table t_student (
id int primary key auto_increment,
name varchar(255),
age int,
cid int
);
insert into t_student values (null, '刘亦菲',16,1);
insert into t_student values (null, 'Thomas',18,100);
select * from t_student;
# 添加外键
drop table t_student;
create table t_student (
id int primary key auto_increment,
name varchar(255),
age int,
cid int,
foreign key(cid) references t_class(id)
);
desc t_student;
show index from t_student;
insert into t_student values (null, '刘亦菲',16,1);
insert into t_student values (null, 'Thomas',18,100);
select * from t_student;
drop table t_student;
create table t_student (
id int primary key auto_increment,
name varchar(255),
age int,
cid int,
constraint fk_cid foreign key(cid) references t_class(id)
);
desc t_student;
show index from t_student;
# 创建表之后再添加外键(不推荐)
drop table t_student;
create table t_student (
id int primary key auto_increment,
name varchar(255),
age int,
cid int
);
show index from t_student;
alter table t_student add constraint fk_cid foreign key(cid) references t_class(id);
show index from t_student;
添加外键之后,对两张表都会有一些约束。具体体现在:
- t_student 表不能随意的添加和修改。
- t_class 表不能随意的删除和修改。
1.级联删除
# 级联删除
drop table t_student;
create table t_student (
id int primary key auto_increment,
name varchar(20) not null,
age int,
cid int,
constraint fk_cid foreign key(cid) references t_class(id) on delete cascade
);
insert into t_student values (null, '刘亦菲',16,1);
delete from t_class where id = 1;
select * from t_student;
select * from t_class;
删除 t_class 表中的记录,那么 t_student 中引用相应记录的行也会一起被删除。
2.级联置空
# 级联置空
drop table t_student;
create table t_student (
id int primary key auto_increment,
name varchar(20) not null,
age int,
cid int,
constraint fk_cid foreign key(cid) references t_class(id) on delete set null
);
insert into t_class values (1, '一班', '赵一');
insert into t_student values (null, '刘亦菲',16,1);
insert into t_student values (null, 'Thomas',18,2);
delete from t_class where id = 1;
select * from t_class;
select * from t_student;
删除 t_class 表中的记录,那么 t_student 中引用相应记录的行的外键会被置为 null 。
3.外键的缺点
外键虽然可以保证表与表之间的参照完整性,但是它的缺点也很明显。
- 影响数据库的性能。
- 在高并发场景中容易引起死锁。
- 当数据量很大的时候,为了保证查询的性能,我们需要进行分库分表。一旦分库分表,我们就不能保证参照的完整性了。
正是因为这些原因,所以《阿里巴巴开发手册》中规定:不要在数据库中设置外键,一切的参照完整都应该在业务层中完成。
当然,这并不是说,外键就一无是处。如果参照完整性都在业务层中完成,也会导致一些问题。
- 业务层与数据耦合了。
- 增加了业务层的逻辑。
- 不能够在数据库的层面保证表之间的参照完整性。
所以,我们应该正确地看待外键。在以下场景中,我们是可以使用外键的。
- 并发度不高。
- 数据量不大,不需要分库分表。
- 正确性 > 性能。
三、UNIQUE
唯一性约束保证了字段的值是唯一的。即使我们有了主键,我们还是可以对其它字段设置唯一性约束。
#3. 唯一性约束
create table t_unique(
id int primary key auto_increment,
a int unique,
b int unique,
c int
);
insert into t_unique(a) values (1); #Y
insert into t_unique(a) values (1); #N
insert into t_unique(a) values (2); #Y
insert into t_unique(a) values (null); #Y
insert into t_unique(a) values (null); #Y
insert into t_unique(a) values (null); #Y
select * from t_unique;
注意事项: null 与 null 是不相同的,所以唯一性约束的字段,可以有多个 null 值。
四、NOT NULL
# 4. 非空性约束
create table t_notnull(
id int primary key auto_increment,
name varchar(255) not null
);
insert into t_notnull values (null, null);
insert into t_notnull values (null, '刘亦菲');
insert into t_notnull(id) values (null);
非空性约束保证了字段的值不为 null ,必须有个具体的值。
五、DEFAULT
# 5. 默认值约束
create table t_default(
id int primary key auto_increment,
name varchar(255) not null default '刘亦菲'
);
insert into t_default(id) values (null);
select * from t_default;
DEFAULT 表示字段的默认值。如果插入数据的时候,没有给该字段取值,就会设置为默认值。
六、CHECK
# 6. 自定义约束 (MySQL未实现)
create table t_height (
height float(3,2) check(height between 0.00 and 3.00)
);
insert into t_height values (1.80);
insert into t_height values (3.80);
select * from t_height;
CHECK 表示自定义约束。MySQL 没有实现这个功能,但是其它商用型数据库,比如 Oracle 是有这个功能的。
#个人学习记录,如发现有错误之处,欢迎与我交流