原创

mysql的约束

温馨提示:
本文最后更新于 2019年01月03日,已超过 1,911 天没有更新。若文章内的图片失效(无法正常加载),请留言反馈或直接联系我

在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表:子表

  1. 创建先创建父表

  2. 删除先删除子表数据

  3. 插入先插入父表数据

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

正文到此结束
本文目录