mysql的约束

在mysql设计表中,有个概念叫做约束

什么是约束

约束英文：constraint

约束实际上就是表中数据的限制条件

 

约束种类

mysql的约束大概分为以下几种:

  1. 非空约束(not null)

  2. 唯一性约束(unique)

  3. 主键约束(primary key) PK

  4. 外键约束(foreign key) FK

  5. 检查约束(目前MySQL不支持、Oracle支持,本文不做介绍)

 

为了能继续看下去,打开mysql,创建了一个表:

1
2
3
4
5
6
7
8
9
create table `test`
(
  `id` int auto_increment primary key , ## 主键约束
  `name` varchar(64) not null unique , ## 非空约束+唯一性约束
  `password` varchar(64) not null default '', ## 非空约束
  `age` tinyint(3) default 0,## 没有约束
  `add_time` datetime ## 没有约束
)engine =InnoDb
charset = 'utf8';

在这个创建语句中,我们使用到了3种约束(非空约束,主键约束,唯一性约束);在后面会使用到外键约束

 

 

非空约束

非空约束是我们最常见的一种约束方式,它规定了我们插入数据不允许为空(在mysql中,''不是空,null才是),例如以下插入语句:

1
2
3
4
insert into `test`(`name`,`password`)value ('test','123456');## 插入成功
insert into `test`(`name`,`password`)value ('test2',null);## password不能为null
insert into `test`(`name`)value ('test3');## 插入成功,password虽然有约束,但是有default '',当未指定password值时,默认为''
insert into `test`(`name`)value ('test');## 唯一性约束,test已经存在

 

仙士可博客

 

应用场景方面,例如用户邮箱,用户密码不能为空,都可以增加非空约束

 

唯一性约束

唯一性约束是使用unique关键字进行的约束,它有多种约束方式以及约束形式.

单字段约束

列级直接约束:

1
2
3
4
create table `unique_test1`(
    `id` int(11) auto_increment primary key ,
    `name` varchar(64) unique
)

表级约束:

1
2
3
4
5
create table `unique_test1`(
    `id` int(11) auto_increment primary key ,
    `name` varchar(64),
    unique (`name`)
)

constraint 关键字约束:

1
2
3
4
5
6
7
8
create table unique_test1
(
  id   int auto_increment
    primary key,
  name varchar(64) null,
  constraint name
    unique (name)
);

以上三种语句最后实现的都是一样的功能

当插入相同的name时,则会报错:

1
insert into `unique_test1` (`name`)values ('仙士可'),('仙士可');

仙士可博客

 

表级多字段约束

我们可以约束多个表字段,来进行联合约束

1
2
3
4
5
6
7
create table `unique_test2`(
    `id` int(11) auto_increment primary key ,
    `name` varchar(64) not null,
    `email` varchar(64),
    `add_time` datetime,
    unique `name_email`(`name`,`email`) ## 前面的是约束别名,后面的是约束字段
)

同时也可以使用constraint 关键字约束:

1
2
3
4
5
6
7
8
9
10
create table unique_test2
(
  id       int auto_increment
    primary key,
  name     varchar(64) not null,
  email    varchar(64) null,
  add_time datetime    null,
  constraint name_email
    unique (name, email)
);

作用都是相同的,

插入数据时,插入同样的name是没有问题的,只有当name+email都相同才会出现问题:

1
2
3
4
5
6
7
8
9
insert into `unique_test2` (`name`, `email`, `add_time`)
values ('仙士可', '1067197739@qq.com', '2018-12-01'),
       ('仙士可', '1067197740@qq.com', '2018-12-01');## 相同name不会出错
insert into `unique_test2` (`name`, `email`, `add_time`)
values ('仙士可1号', '1067197739@qq.com', '2018-12-01'),
       ('仙士可2号', '1067197739@qq.com', '2018-12-01');## 相同email也不会出错   
insert into `unique_test2` (`name`, `email`, `add_time`)
values ('仙士可', '1067197739@qq.com', '2018-12-01'),
       ('仙士可1号', '1067197739@qq.com', '2018-12-01');## 相同name+email将出错

仙士可博客

 

我们也可以在一个表内unique多个字段,让name or email都不允许重复,直接写着3种实现方式:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
create table `unique_test_3`(
       `id` int(11) auto_increment primary key ,
       `name` varchar(64) unique ,
       `email` varchar(64) unique ,
       `add_time` datetime
);
 
create table `unique_test_3`(
       `id` int(11) auto_increment primary key ,
       `name` varchar(64) ,
       `email` varchar(64) ,
       `add_time` datetime,
       unique `name_alias` (`name`),
       unique `email_alias` (`email`)
);
create table unique_test_3
(
       id       int auto_increment
              primary key,
       name     varchar(64) null,
       email    varchar(64) null,
       add_time datetime    null,
       constraint email
              unique (email),
       constraint name
              unique (name)
);

这样的话,只要是插入相同的name或者插入相同的email都将出错:

1
2
3
4
5
6
insert into `unique_test_3` (`name`, `email`, `add_time`)
values ('仙士可', '1067197739@qq.com', '2018-12-01'),
       ('仙士可', '1067197740@qq.com', '2018-12-01');##相同name将出错
insert into `unique_test_3` (`name`, `email`, `add_time`)
values ('仙士可2号', '1067197739@qq.com', '2018-12-01'),
       ('仙士可1号', '1067197739@qq.com', '2018-12-01');##相同email将出错

仙士可博客

 

主键约束

关于主键约束

表中的某个字段添加主键约束后，该字段为主键字段，主键字段中出现的每一个数据都称为主键值.

表中每一行都应该有可以唯一标识自己的一列（或一组列）。

一个顾客可以使用顾客编号列，而订单可以使用订单ID，雇员可以使用雇员ID 或 雇员社会保险号。

主键（primary key） 一列（或一组列），其值能够唯一区分表中的每个行。 

唯一标识表中每行的这个列（或这组列）称为主键。

没有主键，更新或删除表中特定行很困难，因为没有安全的方法保证只涉及相关的行。

在我们平时开发中,大部分情况会给表增加一个'id'的主键,用于标识一行数据

主键也是唯一性约束,一个表中不允许出现2条相同的主键信息

一般情况下,'id'主键会设置成自增(auto_increment)

无论是单一主键还是复合主键，一张表主键约束只能有一个(约束只能有一个，但可以作用到好几个字段)

 

例如:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
create table `unique_test_4`(
       `id` int primary key ,
       `name` varchar(64),
       `email` varchar(64)
) engine =INNODB charset ='utd8';## 单一主键
 
 
create table `unique_test_4`(
       `id` int,
       `name` varchar(64),
       `email` varchar(64),
       primary key `id_name`(`id`,`name`)
) engine =INNODB charset ='utd8';## 表级定义复合主键
 
create table `unique_test_4`(
    `id` int,
    `name` varchar(64),
    `email` varchar(64),
    constraint `id_name` primary key (`id`,`name`)
)engine =INNODB charset ='utd8';## constraint 关键字复合主键

主键的约束和唯一性约束性质差不多,这里就不多做介绍了

 

外键约束

若有两个表A、B，id是A的主键，而B中也有id字段，则id就是表B的外键，外键约束主要用来维护两个表之间数据的一致性。

当表A想要删除一条记录时,必须先将表B的数据删除(如果表B有数据),否则会报错

例如:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
create table member
(
  id       int auto_increment
    primary key,
  name     varchar(64) default '' null,
  password varchar(64) default '' null,
  add_time datetime               null
)
  engine = InnoDB;
create table member_point
(
  id        int auto_increment
    primary key,
  member_id int default 0 null,
  point     int default 0 null,
  constraint con_member_id
    foreign key (member_id) references member (id)
)
  engine = InnoDB;
## 插入数据
insert into `member` (`name`, `password`, `add_time`)
values ('仙士可','123456','20181225'),
       ('仙士可1号','654321','20181212'),
       ('仙士可2号','111111','20181201');  
insert into `member_point` (`member_id`,`point`) values
(1,'100'),
(2,'96'),
(3,'80');

当删除member表id等于1(member_point存在member_id=1数据)的数据,和插入member表不存在的数据时,会报错:

1
2
3
DELETE FROM `test`.`member` WHERE `id` = 1;
insert into `member_point` (`member_id`,`point`) values
     (100,'100')

仙士可博客

仙士可博客

 

注意:

  1. 外键值可以为null

  2. 外键字段去引用一张表的某个字段的时候，被引用的字段必须具有unique约束(主键也算是unique约束)

  3. 有了外键引用之后，表分为父表和子表 

    • member表：父表

    • member_point表：子表

  4. 创建先创建父表

  5. 删除先删除子表数据

  6. 插入先插入父表数据

 

外键约束将会让mysql在插入,删除,更新会增加额外的判断,严格来说,不允许使用外键约束,如果需要限制,请在代码层限制

 

 

 

 

 

 

本文为仙士可原创文章,转载无需和我联系,但请注明来自仙士可博客www.php20.cn

posted @ 2020-05-29 14:29  Kris-Q  阅读(140)  评论(0)  编辑  收藏  举报