SQL反模式学习笔记11 限定列的有效值

目标：限定列的有效值，将一列的有效字段值约束在一个固定的集合中。类似于数据字典。

 

反模式：在列定义上指定可选值

            1、 对某一列定义一个检查约束项，这个约束不允许往列中插入或者更新任何会导致约束失败的值：

　　            create table Bugs(status varchar(20) check(status in('new','in progress','fixed')))。

            2、使用域或者用户自定义类型（UDT）等方法。

　         3、使用触发器：编写一个触发器，当修改指定列的内容时触发，将被修改的值和允许输入的值进行匹配，

                如果不符合则产生一个错误中断操作。

　　缺点：

　　1、 无法查询出所有的约束值来供上层应用程序使用。

           不能用select distinct status from Bugs，因为可能有的status目前还没有存储。

　　　　维护不好的话，还有可能造成列表和数据库存储的值，不一致。

　　2、增加新的约束值，需要修改数据库定义或者触发器。

　　3、废弃或修改某个值，可能要修改大量的数据，风险很大也不合理。

　　4、可移植性差，check约束、域，或者UDT在各种数据库中的支持形式并不统一。

 

如何识别反模式：当出现以下情况时，可能是反模式

　　1、我们不得不将数据库下线，才能在程序中加入一个新的选项。

　　2、这个Status列可以填入这些候选值中的一个。我们不应该改变这个后选值列表。

　　3、程序代码中关于业务规则的选项列表和数据库中的值又不同步了。

 

合理使用反模式：

　　1、在后选值几乎不变化的时候可以使用。

　　2、存储没有业务逻辑且不需要改变的候选值时非常方便。

          比如存储一对二选一切相互对立的值：Left/Right、On/Off等。

　　3、Check约束可以再更多的场景下使用，比如用来检测一个时间区间中start永远小于end。

 

解决方案：创建一张检查表（类似于字典表），每一行包含一个允许在列中出现的候选值，然后在目标表中定义一个外键约束。

              个人经验：在系统中创建一个数据字典表，管理所有可变的候选集合。

结论：在验证固定集合的候选值时使用元数据；

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

 

 

SQL反模式，系列学习汇总

1、SQL反模式学习笔记1 开篇

2、SQL反模式学习笔记2 乱穿马路

3、SQL反模式学习笔记3 单纯的树

4、SQL反模式学习笔记4 建立主键规范【需要ID】

5、SQL反模式学习笔记5 外键约束【不用钥匙的入口】

6、SQL反模式学习笔记6 支持可变属性【实体-属性-值】 

7、SQL反模式学习笔记7 多态关联

8、SQL反模式学习笔记8 多列属性

9、SQL反模式学习笔记9 元数据分裂

10、SQL反模式学习笔记10 取整错误

11、SQL反模式学习笔记11 限定列的有效值

12、SQL反模式学习笔记12 存储图片或其他多媒体大文件

13、SQL反模式学习笔记13 使用索引

14、SQL反模式学习笔记14 关于Null值的使用

15、SQL反模式学习笔记15 分组

16、SQL反模式学习笔记16 使用随机数排序

17、SQL反模式学习笔记17 全文搜索

18、SQL反模式学习笔记18 减少SQL查询数据，避免使用一条SQL语句解决复杂问题

19、SQL反模式学习笔记19 使用*号，隐式的列 

20、SQL反模式学习笔记20 明文密码

21、SQL反模式学习笔记21 SQL注入

22、SQL反模式学习笔记22 伪键洁癖，整理数据