SQL反模式11-每日新花样(限定列的有效值)

目标：限定列的有效值

限定列的有效值是非常有用的，比如bug表的status，我们定义check约束，让它只允许保存(“NEW”,”IN PROGRESS”,”FIXED”)，那么它就会拒绝所有不存在以上集合中的无效值。但是定义check约束，我们日后改如何修改限定有效值呢？这就是本章所讲的反模式。

反模式：在列定义上指定可选值(约束)

很多数据库设计人员趋向于在定义列的时候指定所有可选的有效值。如下DDL：

-- check 约束
create table bug(
	-- other columns
	status varchar(20) check (status in ('NEW','IN PROGRESS','FIXED'))
);
-- 或者使用mysql支持的enum约束
create table bug(
	-- other columns
	status enum('NEW','IN PROGRESS','FIXED')
);

中间的是哪个

假设你开发用户界面，有一个下拉框显示所有的status。你第一反应可能是查询当前正在被使用的值。

1	select distinct status from bug;

然而，如果所有的bug都是新建的，那么你只能得到NEW这个值。还有如果你使用这个查询来填充你的下拉框，这就会变成一个先有鸡还是有蛋的问题。
当然你还有另一种方式，就是获取元数据的值，也就是定义约束的值。

select column_type 
from information_schema.columns
where table_schema = 'bugtracker'
and table_name = 'bug'
and column_name = 'status';

但是你还是无法简单的获取到有效值。如果mysql，以上会返回一个类型为longtext，内容为enum(‘NEW’,’IN PROGRESS’,’FIXED’)的结果，其中包含括号、逗号、引号。你必须编写一点额外的程序来解析这个字符串。

添加新口味

没有什么语法能后从enum或者check约束中，添加或删除一个值，你只能使用一个新的集合定义到这个列。如添加一个DUPLICATE的值。

1	alter table bug modify column status enum('NEW','IN PROGRESS','FIXED', 'DUPLICATE');

这个操作会重新构建一张使用的表，在生产环境应该是极少的，开销也是巨大的。修改了元数据，意味着你要花更大的力气来测试稳定性。

老的口味永不消失

如果你打算废弃一个状态，比如，将FIXED状态拆分成CODE COMPLETE和VERIFIED两个状态：

1	alter table bug modify column status enum('NEW','IN PROGRESS','CODE COMPLETE', 'VERIFIED');

但是大多数情况下，这条语句会执行错误，因为表数据中存在FIXED的值。

可移植性低

check约束、域和UDT在各数据库的支持形式是不一致的。enum更加是mysql独有的。

如何识别反模式：

当出现以下情况时，可能是反模式

我们不得不将数据库下线，才能在程序中加入一个新的选项。
这个Status列可以填入这些候选值中的一个。我们不应该改变这个后选值列表。
程序代码中关于业务规则的选项列表和数据库中的值又不同步了。

解决方案：在数据中指定值

也就是创建一张检查表bug_status，其中bug中每一行的值为bug_status中的数据。

create table bug_status(
	status varchar(20) primary key
);
insert into bug_status(status) values('NEW'),('IN PROGRESS'),('FIXED');

create table bug(
	-- other columns
	status varchar(20),
	foreign key (status) references bug_status(status) on update cascade
);

该设计能让你有效的更新检查表中的值

增加一个选项

1	insert into bug_status(status)values('DUPLICATE');

如果外键使用了on update cascade，重命名一个选项

1	update bug_status set status = 'INVALID' where status = 'BOGUS';

支持废弃的数据

1
2
3

alter table bug_status add column active enum('INACTIVE','ACTIVE') not null default 'ACTIVE';
update bug_status set active = 'INACTIVE' where status ='DUPLICATE';
select * from bug_status where active = 'ACTIVE';

结论：

在验证固定集合的候选值时使用元数据；
在验证可变集合的候选值时使用数据。