数据库实践:增加外键约束

个人有个不知道是好是坏的习惯,就是一个project写schema的sql的时候一般不写明外键,只是在注释里面标记。这样有一个好处,就是对数据或者表格进行插入、删除操作的时候更方便,否则有时候有依赖关系,你还要去处理依赖,不够自由。

事实上,对于传统关系型数据库,增加外键还是很必要的,一是可以及时以异常的形式及早告知逻辑错误,二是所谓用程序逻辑控制依赖关系、外键降低性能在小项目中都是扯淡。NoSQL有时候用多了也会把人带坏。

言归正传,直接拿项目中的SQL了。

一个简单的设计,一级分类和二级分类,一对多的关系。

create table if not exists level1category (
    id int not null auto_increment,
    title varchar(128) not null,
    primary key (id),
    unique key title (title)
) engine=innodb default charset=utf8 auto_increment=1;

create table if not exists level2category (
    id int not null auto_increment,
    title varchar(128) not null,
    level1id int not null,
    primary key (id),
    unique key title (title),
) engine=innodb default charset=utf8 auto_increment=1;

增加外键(也可以直接ALTER TABLE level2category ADD FOREIGN KEY (level1id) REFERENCES level1category(id)

create table if not exists level1category (
    id int not null auto_increment,
    title varchar(128) not null,
    primary key (id),
    unique key title (title)
) engine=innodb default charset=utf8 auto_increment=1;

create table if not exists level2category (
    id int not null auto_increment,
    title varchar(128) not null,
    level1id int not null,
    primary key (id),
    unique key title (title),
    constraint level2category_fk_1 foreign key (level1id) references level1category(id)
) engine=innodb default charset=utf8 auto_increment=1;

因为增加外键之前里面有一些问题数据,删掉依赖关系出错的

delete from level2category where level1id not in (select id from level1category);

因为有外键的关联,insert或create table语句就需要按依赖顺序(原来没外键的时候,你的文件中的sql语句顺序可能只是天马行空随便写的),摆脱这个问题可以先

SET FOREIGN_KEY_CHECKS = 0;

之后再

SET FOREIGN_KEY_CHECKS = 1;

把这两行分别放在sql文件首尾即可。

因为是个nodejs项目,所以关于数据库约束的error处理部分我是这么写的(随意摘录一段代码好了,一个foreign key约束一个unique key约束)

// 在某个callback中
if (err && err.code === 'ER_ROW_IS_REFERENCED_') {
    callback(new Error('存在约束'));
} else if (err && err.code === 'ER_DUP_ENTRY') {
    callback(new Error('名称已存在'));
} else if (err) {
    callback(new Error('其他错误'));
} else {
    callback(null)
}

扯完了,好久没发新的blog了,主要是懒,感觉zhoutall.com这个域名啥时候得续费了…今天扯一篇主要是纪念下第一个外包赚钱的项目完美结束~

好吧查了下,我当年很机智的把zhoutall.com买到2017年了…

Tags :

One thought on “数据库实践:增加外键约束”

  1. 买到2017太机智…我上一个 .com 域名过期后就基本不想再折腾了…后来 Github 非常良心地送了一年的 .me 域名才重新维护起来

发表评论

电子邮件地址不会被公开。 必填项已用*标注

Click the right image To submit your comment: