mysql的约束

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

什么是约束

约束英文:constraint

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


约束种类

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

  1. 非空约束(not null)

  2. 唯一性约束(unique)

  3. 主键约束(primary key) PK

  4. 外键约束(foreign key) FK

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


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

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才是),例如以下插入语句:

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关键字进行的约束,它有多种约束方式以及约束形式.

单字段约束

列级直接约束:

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

表级约束:

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

constraint 关键字约束:

create table unique_test1
(
  id   int auto_increment
    primary key,
  name varchar(64) null,
  constraint name
    unique (name)
);

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

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

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

仙士可博客


表级多字段约束

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

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 关键字约束:

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都相同才会出现问题:

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种实现方式:

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都将出错:

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)

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


例如:

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有数据),否则会报错

例如:

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表不存在的数据时,会报错:

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在插入,删除,更新会增加额外的判断,严格来说,不允许使用外键约束,如果需要限制,请在代码层限制






仙士可博客
请先登录后发表评论
  • 最新评论
  • 总共1条评论
仙士可博客

仙士可:test

2019-01-03 17:47:34 回复

  • 本站由白俊遥博客程序搭建
    © 2017-1-17 php20.cn 版权所有 ICP证:闽ICP备17001387号
  • 联系邮箱:1067197739@qq.com