利用11G的虚拟列实现复杂的外键约束

newkid

在上贴的test_parent表中，因为delete_flag恒为N, 所以其实也可作为主键的一部分：

CREATE TABLE test_parent (
       parent_id    NUMBER(10)
      ,deleted_date DATE
      ,delete_flag  VARCHAR2(1) AS ('N')      -------- 虚拟一个永远为 N 的删除标记
       );

ALTER TABLE test_parent ADD CONSTRAINT test_parent_pk PRIMARY KEY (parent_id,delete_flag);

这样的话，原来的parent_id主键索引就可以省下来了。就是硬凑出个常量作主键显得有点怪。还有个副作用就是CBO不知道parent_id是唯一的，这样查询计划就会受影响。

[ 本帖最后由 newkid 于 2010-7-8 08:53 编辑 ]

newkid

7楼的那一个UPDATE语句：
UPDATE test_parent SET deleted_date = DATE '2010-6-1' WHERE parent_id=1;
顺利通过，没有引发子表上的校验，这是令人遗憾的地方，另外还有一个小小的隐患：
我们声明为DETERMINISTIC的函数f_parent_id, 其实是不确定的，传入相同的parent_id参数，返回值可能随父表的数据而变化。
就这点而言我们欺骗了ORACLE。如果在虚拟列上有索引，这个其实已经陈旧的数据就会被“物化”在索引中，假设某个查询条件用到了虚拟列，那么就可能返回错误答案。
因此如果使用了这个技巧，要注意不能把虚拟列用于查询。

yangtingkun

原帖由 newkid 于 2010-7-7 23:41 发表


杨兄可否把“将CATAGORY独立成维度表”的思路展开一下？是不是在我2楼例子的order_products表增加CATEGORY列？如果是这样也不简单，因为这个CATEGORY列必须和orders表的匹配，还要和products匹配。

怎么设计都会比较复杂：

CREATE TABLE products (
       product_id    NUMBER PRIMARY KEY
      ,product_name  VARCHAR2(20)
      ,category    VARCHAR2(20)
      ,primary key (product_id, category)
       );


CREATE TABLE orders (
       order_id      NUMBER
      ,category     VARCHAR2(20)         ----- 假设一个订单只能订购某一种类别的产品
    , primary key (order_id, category)
       );


CREATE TABLE order_products (   ---- 每个订单包含哪些产品
       order_id      NUMBER
       ,category       VARCHAR2(20)
      ,product_id    NUMBER      
      ,quantity      NUMBER
      ,PRIMARY KEY (order_id,product_id)
      ,FOREIGN KEY (ORDER_ID, CATEGORY) REFERENCES ORDERS
      ,FOREIGN KEY (PRODUCT_ID, CATEGORY) REFERENCES PRODUCTS
      );


其实完全靠约束是不可能解决所有数据问题的
虚拟列可以解决ORDER_PRODUCTS表数据的合法性问题，但是解决不了PRODUCTS表的合法性问题：
INSERT INTO products VALUES (7,'水彩笔','服装类');

nyfor

牛!  才看到, 下班了, 先留个脚印, 再慢慢看.

嗯,不错, 又学到新东西了, 谢谢 newkid.

[ 本帖最后由 nyfor 于 2010-7-8 20:05 编辑 ]

newkid

原帖由 yangtingkun 于 2010-7-8 11:59 发表

怎么设计都会比较复杂：

CREATE TABLE products (
       product_id    NUMBER PRIMARY KEY
      ,product_name  VARCHAR2(20)
      ,category    VARCHAR2(20)
      ,primary key (product_id, category)
       );


CREATE TABLE orders (
       order_id      NUMBER
      ,category     VARCHAR2(20)         ----- 假设一个订单只能订购某一种类别的产品
    , primary key (order_id, category)
       );


CREATE TABLE order_products (   ---- 每个订单包含哪些产品
       order_id      NUMBER
       ,category       VARCHAR2(20)
      ,product_id    NUMBER      
      ,quantity      NUMBER
      ,PRIMARY KEY (order_id,product_id)
      ,FOREIGN KEY (ORDER_ID, CATEGORY) REFERENCES ORDERS
      ,FOREIGN KEY (PRODUCT_ID, CATEGORY) REFERENCES PRODUCTS
      );


其实完全靠约束是不可能解决所有数据问题的
虚拟列可以解决ORDER_PRODUCTS表数据的合法性问题，但是解决不了PRODUCTS表的合法性问题：
INSERT INTO products VALUES (7,'水彩笔','服装类');

这样一来你就有逻辑主键+业务主键构成的混合型主键，挺新颖的！
不过你也可以保留原来的id做逻辑主键，只要在id,category上再建唯一约束就可以了。代价就是会多一个索引。
这个'水彩笔'的例子也太霸道了吧，外键当然约束不了父表上的数据。不过有了这个外键之后，如果有人想在一批订货中包含'水彩笔'和其他文具，就会报错，从一定程度上也能帮助发现错误！

总结一下这个虚拟列作外键的用法，无非就是想在外键约束加入其他条件：
1. 如果这个条件可表示为同一行的列间关系：
在父表上可以用CASE表达式、甚至规则表达式凑出一个是否可用的标志位来。
在子表上同样可以用各种方法凑出标志位，如果不需要引用让它返回NULL, 如果需要引用而且合法则返回父表上的相同标志，如果不合法让它返回一个父表中没有的标记。
2. 如果这个条件是行间关系：想不出有什么办法。
3. 如果这个条件是表间关系：可以用伪装成DETERMINISTIC的函数，但由于它不是真正确定性的函数，所以有漏洞。

杨兄说那个delete_flag的例子其实更复杂，可否分享一下？

newkid

感谢 Frank Zhou 给我提供的资料，类似的思路早就有人实践过了：

http://asktom.oracle.com/pls/ask ... #678658300346935451

TOM 用了个简洁的例子证明它的威力：
ops$tkyte%ORA11GR1> create table c
  2  ( a varchar2(10),
  3    b as (substr( a, 5, 2 )) references p
  4  );

这位Lucas Jellema的博客：
http://technology.amis.nl/blog/2 ... oring-them-and-more
他的第一个例子和我#6的例子很像；
第二个例子逻辑上不合理：招聘的新员工必须检查同年是否招聘过，如果有则允许，没有则不允许。那么第一个就永远无法招进来了！
但他这个例子无非是想给读者一个启发。思路就是利用MVIEW复制旧数据，这样就把一些行间关系转换为表间关系。

他在另一篇还写了如何用虚拟列、自定义函数和CHECK约束实现一些复杂校验：
http://technology.amis.nl/blog/2 ... amic-business-rules


这位 Alex Nuijten 的博客：
http://technology.amis.nl/blog/3 ... -columns-the-sequel
他的用途和我在1楼的“统一编码表”相同，但实现方法相反，我是在子表上虚拟出“编码类型”，他是在父表上做文章，虚拟出很多不同的唯一键来。如果是子表数目多，用他的方法更胜一筹。

wanglina

如此神奇！

yangtingkun

原帖由 newkid 于 2010-7-8 22:12 发表


这样一来你就有逻辑主键+业务主键构成的混合型主键，挺新颖的！
不过你也可以保留原来的id做逻辑主键，只要在id,category上再建唯一约束就可以了。代价就是会多一个索引。
这个'水彩笔'的例子也太霸道了吧，外键当然约束不了父表上的数据。不过有了这个外键之后，如果有人想在一批订货中包含'水彩笔'和其他文具，就会报错，从一定程度上也能帮助发现错误！

总结一下这个虚拟列作外键的用法，无非就是想在外键约束加入其他条件：
1. 如果这个条件可表示为同一行的列间关系：
在父表上可以用CASE表达式、甚至规则表达式凑出一个是否可用的标志位来。
在子表上同样可以用各种方法凑出标志位，如果不需要引用让它返回NULL, 如果需要引用而且合法则返回父表上的相同标志，如果不合法让它返回一个父表中没有的标记。
2. 如果这个条件是行间关系：想不出有什么办法。
3. 如果这个条件是表间关系：可以用伪装成DETERMINISTIC的函数，但由于它不是真正确定性的函数，所以有漏洞。

杨兄说那个delete_flag的例子其实更复杂，可否分享一下？

其实从原理上你已经实现了，不过你的例子只考虑了主表利用DELETE_FLAG表示删除的情况，如果子表本身如果也用DELETE_FLAG表示删除的话，需要设计外键就会更复杂一些。

yangtingkun

想了一下，也不算太复杂：

CREATE TABLE T_FATHER
(ID NUMBER,
NAME VARCHAR2(30),
DELETE_FLAG VARCHAR2(1));

CREATE TABLE T_CHILDREN
(ID NUMBER,
FID NUMBER,
F_FLAG AS (CAST (DECODE(DELETE_FLAG, 'Y', NULL, 'N') AS VARCHAR2(1))),
NAME VARCHAR2(30),
DELETE_FLAG VARCHAR2(1));

ALTER TABLE T_FATHER ADD
PRIMARY KEY (ID, DELETE_FLAG);
ALTER TABLE T_CHILDREN ADD
FOREIGN KEY (FID, F_FLAG)
REFERENCES T_FATHER;

dingjun123

牛，好帖子要经常看看！