查看: 12291|回复: 22

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

[复制链接]
论坛徽章:
540
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2010-7-7 02:46 | 显示全部楼层 |阅读模式
前阵子有个关于外键的争论贴,yangtingkun提出了一个有趣的话题:
http://www.itpub.net/thread-1313696-4-3.html
“前一段时间,一个系统为了避免出现DELETE语句,采用了DELETE_FLAG的方式实现。这就带来了一个问题,Oracle提供的主外键功能不满足要求,于是为了实现满足这种情况下的主外键关系,只好自己实现外键的功能。一旦你自己考虑并发、锁以及多版本一致性的问题,你就会发现Oracle的外键为你实现了多少的功能,而如果你考虑的不周全,就会导致违反约束的数据产生。”

有没有办法让ORACLE来实现带条件的引用约束呢?当时我想了一个办法用ON COMMIT刷新的物化视图来作为新的父表,在子表上则建延迟外键约束。但杨兄指出如果在同一个事务里同时更新父子表会有问题。

测试表:
CREATE TABLE test_parent (
       parent_id   NUMBER(10)  PRIMARY KEY
      ,delete_flag VARCHAR2(1)
      ,CHECK (delete_flag IN ('Y','N'))
       );

那么我们现在就需要限制子表只引用delete_flag='N'的记录,delete_flag='Y'认为已经删除了不能引用。

今天我偶然想到11G的虚拟列功能可以用在这里,于是进行了如下测试:

在父表上增加唯一约束,这个约束看似多余但是在后面有用:

ALTER TABLE test_parent ADD CONSTRAINT parent_uk UNIQUE (parent_id,delete_flag);

建立子表:
CREATE TABLE test_child (
       child_id    NUMBER(10)  PRIMARY KEY
      ,parent_id   NUMBER(10)
       );

ALTER TABLE test_child ADD CONSTRAINT child_fk FOREIGN KEY (parent_id,'N') REFERENCES test_parent(parent_id,delete_flag);

ERROR at line 1:
ORA-00904: : invalid identifier

ORACLE不让我们用常量来做引用列。

增加一个虚拟列:

ALTER TABLE test_child ADD delete_flag VARCHAR2(1) AS ('N');

在这上面建外键:

ALTER TABLE test_child ADD CONSTRAINT child_fk FOREIGN KEY (parent_id,delete_flag) REFERENCES test_parent(parent_id,delete_flag);

测试:
jsu@JSU> INSERT INTO test_parent(parent_id,delete_flag) VALUES (1,'N');

1 row created.

jsu@JSU> INSERT INTO test_parent(parent_id,delete_flag) VALUES (2,'Y');

1 row created.

jsu@JSU> INSERT INTO test_child(child_id,parent_id) VALUES (1,1);

1 row created.

jsu@JSU> INSERT INTO test_child(child_id,parent_id) VALUES (2,2);
INSERT INTO test_child(child_id,parent_id) VALUES (2,2)
*
ERROR at line 1:
ORA-02291: integrity constraint (JSU.CHILD_FK) violated - parent key not found


最后一个插入如愿以偿地失败了,因为父亲记录不满足delete_flag='N'.

jsu@JSU> UPDATE test_parent SET delete_flag='N' WHERE parent_id=2;

1 row updated.

jsu@JSU> INSERT INTO test_child(child_id,parent_id) VALUES (2,2);

1 row created.

可见,把标记修改之后,相应的子表数据得以插入,而且是在同一个事务。

把标记修改为Y, 它会自动检测是否有子记录:
UPDATE test_parent SET delete_flag='Y' WHERE parent_id=1;
*
ERROR at line 1:
ORA-02292: integrity constraint (JSU.CHILD_FK) violated - child record found

这也是我们想要的效果。

空外键测试:

INSERT INTO test_child(child_id,parent_id) VALUES (3,NULL);

1 row created.

NULL被顺利插入。

COMMIT;

INSERT INTO test_child(child_id,parent_id) VALUES (4,2);

故意不提交,打开另一个窗口,对虚拟列的数据进行更新:
UPDATE test_parent SET delete_flag='N' WHERE parent_id=2;

被挂住了,因为子表的虚拟列上没建索引。全部回滚后建索引:

CREATE INDEX test_child_vx ON test_child(parent_id,delete_flag);

再重复上述插入、更新,阻塞没有发生,这和普通外键的表现是一样的。

再来一个应用实例:统一编码表
假设我们有一些类似的编码,决定用一张统一的表来进行管理。

CREATE TABLE lookup
   ( lookup_id   NUMBER(4)    PRIMARY KEY,
     lookup_type VARCHAR2(10) NOT NULL,
     lookup_name VARCHAR2(20)
    );

ALTER TABLE lookup ADD CONSTRAINT lookup_uk UNIQUE(lookup_id,lookup_type);

INSERT INTO lookup VALUES(1001,'education','大专');
INSERT INTO lookup VALUES(1002,'education','本科');
INSERT INTO lookup VALUES(1003,'education','硕士');
INSERT INTO lookup VALUES(2001,'salary'   ,'一级');
INSERT INTO lookup VALUES(2002,'salary'   ,'二级');
INSERT INTO lookup VALUES(2003,'salary'   ,'三级');

CREATE TABLE emp (
       emp_id        NUMBER PRIMARY KEY
      ,name          VARCHAR2(20)
      ,education     NUMBER(4)
      ,salary_level  NUMBER(4)
      );

这时候,我们可以分别在education,salary_level列上分别建立到lookup的索引,但是存在一个问题:你无法保证education插入的都是学历的数据,比方说education=2001这样的错误就检查不出来。
还是利用虚拟列:

ALTER TABLE emp ADD (education_type AS (CAST('education' AS VARCHAR2(10))),salary_type AS (CAST('salary' AS VARCHAR2(10))));

再建立外键:

ALTER TABLE emp ADD CONSTRAINT emp_fk1 FOREIGN KEY (education,education_type) REFERENCES lookup(lookup_id,lookup_type);
ALTER TABLE emp ADD CONSTRAINT emp_fk2 FOREIGN KEY (salary_level,salary_type) REFERENCES lookup(lookup_id,lookup_type);

INSERT INTO emp (emp_id,name,education,salary_level) VALUES (1,'jack',1001,1001);

ERROR at line 1:
ORA-02291: integrity constraint (JSU.EMP_FK2) violated - parent key not found

INSERT INTO emp (emp_id,name,education,salary_level) VALUES (1,'jack',1001,2001);

1 row created.

注意在建立虚拟列的时候用了CAST, 如果我不用CAST的话:
ALTER TABLE emp ADD (education_type VARCHAR2(10) AS ('education'),salary_type VARCHAR2(10) AS ('salary'));

虽然这样也可以建立外键,但是插入的时候就会报父键找不到的错误,哪怕你的数据是正确的。第一个例子中VARCHAR2(1)没有用CAST也成功了,可能是ORACLE还有BUG吧。
论坛徽章:
540
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2010-7-7 04:49 | 显示全部楼层
再来看一个简化的产品订单的例子:

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


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


CREATE TABLE order_products (   ---- 每个订单包含哪些产品
       order_id      NUMBER REFERENCES orders(order_id)
      ,product_id    NUMBER REFERENCES products(product_id)
      ,quantity      NUMBER
      ,PRIMARY KEY (order_id,product_id)
      );
      

INSERT INTO products VALUES (1,'铅笔','文具类');
INSERT INTO products VALUES (2,'钢笔','文具类');
INSERT INTO products VALUES (3,'圆珠笔','文具类');

INSERT INTO products VALUES (4,'牛仔裤','服装类');
INSERT INTO products VALUES (5,'鞋子','服装类');
INSERT INTO products VALUES (6,'帽子','服装类');

INSERT INTO orders VALUES (1,'文具类');

现在的问题是:
怎样限制order_products中订单1的产品全部都是文具类?

我想到的方法:
1. 利用WITH CHECK OPTION的视图:

CREATE VIEW vw_order_products
AS SELECT order_id
         ,product_id
         ,quantity
     FROM order_products op
   WHERE NOT EXISTS
         (SELECT NULL
           FROM products p,orders o
           WHERE p.product_id = op.product_id
                 AND o.order_id = op.order_id
                 AND p.category<>o.category
          )
WITH CHECK OPTION;

INSERT INTO vw_order_products VALUES (1,4,100);

ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

对视图插入数据会引发约束检查,但是如果直接插入表就不起作用了:
INSERT INTO order_products VALUES (1,4,100);

1 row created.

ROLLBACK;

2. 利用虚拟列
CREATE OR REPLACE FUNCTION f_get_category(p_order_id IN NUMBER)
RETURN VARCHAR2 DETERMINISTIC
AS
   lv_category orders.category%TYPE;
BEGIN
   SELECT category INTO lv_category FROM orders WHERE order_id = p_order_id;
   RETURN lv_category;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
        RETURN '';
END f_get_category;
/

order_products表本来是没有category这个列的,我们用这个函数把它虚拟出来:

ALTER TABLE order_products ADD category AS (CAST(f_get_category(order_id) AS VARCHAR2(20)));

其他的如法炮制:
ALTER TABLE products ADD CONSTRAINT products_uk UNIQUE(product_id,category);

ALTER TABLE order_products ADD CONSTRAINT order_products_fk FOREIGN KEY (product_id,category) REFERENCES products(product_id,category);

测试一下:
INSERT INTO order_products(order_id,product_id,quantity) VALUES (1,4,100);
*
ERROR at line 1:
ORA-02291: integrity constraint (JSU.ORDER_PRODUCTS_FK) violated - parent key not found

INSERT INTO order_products(order_id,product_id,quantity) VALUES (1,2,100);

1 row created.

使用道具 举报

回复
论坛徽章:
401
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2010-7-7 07:23 | 显示全部楼层
学习

使用道具 举报

回复
论坛徽章:
226
BLOG每日发帖之星
日期:2010-02-11 01:01:06紫蛋头
日期:2013-01-12 23:45:222013年新春福章
日期:2013-02-25 14:51:24问答徽章
日期:2013-10-17 18:06:40优秀写手
日期:2013-12-18 09:29:10马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
发表于 2010-7-7 09:15 | 显示全部楼层
非常巧妙的思路

实际使用的时候会更复杂,因为不光主表要考虑DELETE_FLAG,子表也有同样的问题,这将会导致外键的设置更加的复杂

BTW:利用虚拟列来实现这种复杂的外键依赖关系确实很巧妙,不过要是我的话会考虑将CATAGORY独立成维度表

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
484
马上有钱
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31ITPUB年度最佳版主
日期:2015-03-18 15:48:48
发表于 2010-7-7 20:59 | 显示全部楼层
前排,跟在杨大师后面学习

使用道具 举报

回复
论坛徽章:
540
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2010-7-7 21:31 | 显示全部楼层
在那贴中,反方大将ruideliang提出一个难题,如果在子表中只有一个外键列,无法用它来指向不同的父表。

现在有了虚拟列,该难题被轻易化解:

先建立两个父表和测试数据:
CREATE TABLE data_source1 (
       source_id    NUMBER PRIMARY KEY
      ,source_name  VARCHAR2(20)
      );


CREATE TABLE data_source2 (
       source_id    NUMBER PRIMARY KEY
      ,source_name  VARCHAR2(20)
      );

INSERT INTO data_source1 VALUES (1,'source A');
INSERT INTO data_source1 VALUES (2,'source B');
INSERT INTO data_source1 VALUES (3,'source C');

INSERT INTO data_source2 VALUES (4,'source D');
INSERT INTO data_source2 VALUES (5,'source E');
INSERT INTO data_source2 VALUES (6,'source F');

建立子表:
CREATE TABLE test_data (
       data_id      NUMBER PRIMARY KEY
      ,source_type  NUMBER        ------ source_type=1 表示指向data_source1, =2表示指向data_source2
      ,source_id    NUMBER        ------ 这个source_id可以根据source_type的不同指向data_source1或data_source2
      ,the_data     NUMBER
      ,source_id1 AS (DECODE(source_type,1,source_id))  --- 把一个source_id虚拟为两个列
      ,source_id2 AS (DECODE(source_type,2,source_id))
      );

ALTER TABLE test_data ADD CONSTRAINT test_data_fk1 FOREIGN KEY (source_id1) REFERENCES data_source1(source_id);
ALTER TABLE test_data ADD CONSTRAINT test_data_fk2 FOREIGN KEY (source_id2) REFERENCES data_source2(source_id);

测试:

jsu@JSU> INSERT INTO test_data (data_id,source_type,source_id,the_data) VALUES (1,1,1,1);

1 row created.     ---- 成功插入

jsu@JSU> INSERT INTO test_data (data_id,source_type,source_id,the_data) VALUES (2,1,4,2);
INSERT INTO test_data (data_id,source_type,source_id,the_data) VALUES (2,1,4,2)
*
ERROR at line 1:  ----- 失败,这是因为source_type=1并且source_id=4在data_source1中找不到
ORA-02291: integrity constraint (JSU.TEST_DATA_FK1) violated - parent key not found


jsu@JSU> INSERT INTO test_data (data_id,source_type,source_id,the_data) VALUES (2,2,4,2);

1 row created.  ---- 把source_type改为2, 成功插入

我比较喜欢的解决办法,要么是把父表合并为一张(这是真正的“可扩展”的解决办法),要么是在子表上建两个不同的外键列。
虚拟列的办法其实是后一种的变通。

使用道具 举报

回复
论坛徽章:
540
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2010-7-7 23:33 | 显示全部楼层
现在我把第一个例子稍微改一下,不再是简单的删除标记,而是一个删除日期。删除日期为空可以被引用,非空则不允许引用。
DROP TABLE test_child;
DROP TABLE test_parent;

CREATE TABLE test_parent (
       parent_id    NUMBER(10)  PRIMARY KEY
      ,deleted_date DATE
      ,delete_flag VARCHAR2(1) AS (CASE WHEN deleted_date IS NOT NULL THEN 'Y' ELSE 'N' END)  ---- 利用虚拟列构造出原来的delete_flag
       );

ALTER TABLE test_parent ADD CONSTRAINT test_parent_uk UNIQUE (parent_id,delete_flag);

其他照旧:
CREATE TABLE test_child (
       child_id    NUMBER(10)  PRIMARY KEY
      ,parent_id   NUMBER(10)
      ,delete_flag VARCHAR2(1) AS ('N')     ---- 用一个常量做删除标记
       );

ALTER TABLE test_child ADD CONSTRAINT child_fk FOREIGN KEY (parent_id,delete_flag) REFERENCES test_parent(parent_id,delete_flag);

用同样的测试例子,得到同样的效果:
jsu@JSU> INSERT INTO test_parent(parent_id,deleted_date) VALUES (1,NULL);

1 row created.


jsu@JSU> INSERT INTO test_parent(parent_id,deleted_date) VALUES (2,SYSDATE);

1 row created.


jsu@JSU> INSERT INTO test_child(child_id,parent_id) VALUES (1,1);

1 row created.


jsu@JSU> INSERT INTO test_child(child_id,parent_id) VALUES (2,2);
INSERT INTO test_child(child_id,parent_id) VALUES (2,2)
*
ERROR at line 1: ---- 因为parent_id=2的父记录删除日期非空
ORA-02291: integrity constraint (JSU.CHILD_FK) violated - parent key not found


jsu@JSU> UPDATE test_parent SET deleted_date=NULL WHERE parent_id=2;

1 row updated.


jsu@JSU> INSERT INTO test_child(child_id,parent_id) VALUES (2,2);

1 row created.   ---- 把parent_id=2的父记录删除日期修改为空,则顺利插入


jsu@JSU> UPDATE test_parent SET deleted_date=SYSDATE WHERE parent_id=1;
UPDATE test_parent SET deleted_date=SYSDATE WHERE parent_id=1
*
ERROR at line 1:
ORA-02292: integrity constraint (JSU.CHILD_FK) violated - child record found



jsu@JSU> INSERT INTO test_child(child_id,parent_id) VALUES (3,NULL);

1 row created.

这个例子说明父、子表都可以利用各种表达式(甚至自定义函数)构造虚拟列,实现复杂的约束关系。


下面是一个不太完美的例子,我试图在引用关系中增加其他条件。这个复杂的引用规则如下:
如果删除日期为空则可被引用;如果删除日期非空,只有在该日期之前被创建的子表记录可以引用,超过该日期的则不可引用。

********************************************************************

DROP TABLE test_child;
DROP TABLE test_parent;

CREATE TABLE test_parent (
       parent_id    NUMBER(10)  PRIMARY KEY
      ,deleted_date DATE
       );


创建测试数据:
INSERT INTO test_parent(parent_id,deleted_date) VALUES (1,NULL);
INSERT INTO test_parent(parent_id,deleted_date) VALUES (2,DATE '2009-1-1');
INSERT INTO test_parent(parent_id,deleted_date) VALUES (3,DATE '2010-1-1');

创建一个函数判断子表数据创建日期是否在删除之前,如果是则返回父表id, 否则返回一个永远无效的ID(这里我使用了-1):
CREATE OR REPLACE FUNCTION f_parent_id(p_parent_id IN NUMBER,p_child_created_date DATE)
RETURN NUMBER DETERMINISTIC
AS
   lv_deleted_date DATE;
BEGIN
   IF p_parent_id IS NULL THEN
      RETURN NULL;
   END IF;
   
   SELECT deleted_date INTO lv_deleted_date FROM test_parent WHERE parent_id = p_parent_id;
   RETURN CASE WHEN p_child_created_date>lv_deleted_date THEN -1 ELSE p_parent_id END;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
        RETURN -1;
END f_parent_id;
/


CREATE TABLE test_child (
       child_id      NUMBER(10)  PRIMARY KEY
      ,parent_id     NUMBER(10)
      ,created_date  DATE
      ,vir_parent_id AS (f_parent_id(parent_id,created_date))
       );

ALTER TABLE test_child ADD CONSTRAINT test_child_fk FOREIGN KEY (vir_parent_id) REFERENCES test_parent(parent_id);

子表数据测试:

jsu@JSU> INSERT INTO test_child(child_id,parent_id,created_date) VALUES (1,1,SYSDATE-1);

1 row created.

jsu@JSU> INSERT INTO test_child(child_id,parent_id,created_date) VALUES (2,1,SYSDATE);

1 row created.

jsu@JSU> INSERT INTO test_child(child_id,parent_id,created_date) VALUES (3,2,SYSDATE-1);
INSERT INTO test_child(child_id,parent_id,created_date) VALUES (3,2,SYSDATE-1)
*
ERROR at line 1:   ------ 这是因为created_date 在删除日期之后
ORA-02291: integrity constraint (JSU.TEST_CHILD_FK) violated - parent key not found

jsu@JSU> INSERT INTO test_child(child_id,parent_id,created_date) VALUES (3,2,DATE '2007-10-1');

1 row created.     ----- 改为删除日期之前则顺利插入

一切似乎很顺利,但是当我试图修改父表数据就出问题了:
jsu@JSU> UPDATE test_parent SET deleted_date = DATE '2010-6-1' WHERE parent_id=1;

1 row updated.

我们明明有两条在2010-6-1之后创建的子表记录,但是deleted_date并不是被引用列,所以ORACLE不会重新检查约束。

jsu@JSU> UPDATE test_child SET created_date = created_date WHERE child_id=1;
UPDATE test_child SET created_date = created_date WHERE child_id=1
*
ERROR at line 1: ------- 修改子表会触发校验
ORA-02291: integrity constraint (JSU.TEST_CHILD_FK) violated - parent key not found

这个例子虽然不够完美,但如果你的数据是按照自然发生的时间戳来记录,也可以满足需求。

使用道具 举报

回复
论坛徽章:
540
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2010-7-7 23:41 | 显示全部楼层
原帖由 yangtingkun 于 2010-7-7 09:15 发表
非常巧妙的思路

实际使用的时候会更复杂,因为不光主表要考虑DELETE_FLAG,子表也有同样的问题,这将会导致外键的设置更加的复杂

BTW:利用虚拟列来实现这种复杂的外键依赖关系确实很巧妙,不过要是我的话会考虑将CATAGORY独立成维度表


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

使用道具 举报

回复
论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
发表于 2010-7-7 23:43 | 显示全部楼层
很有意义,很牛!~

使用道具 举报

回复
论坛徽章:
540
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2010-7-8 02:44 | 显示全部楼层
在7楼的后一个例子,我在子表上建立了vir_parent_id,但是没有在这列上建索引。
大部分情况下,父表的主键只有插入,没有删改(删除的用删除日期非空来表示)。所以这个列上不建索引没什么问题。
但是如果情况比这复杂,父表也出现了删改的需求,那么就只好在vir_parent_id建索引。这个索引纯粹是为了防止锁表用的,不能做其他查询条件用,为了查询还得在parent_id上建索引。

假如一张父表被许多子表引用,而且子表的数据量比父表大(因为每个父记录都有多个子记录),在很多子表上建双重索引(parent_id建一个,vir_parent_id又建一个)就显得有些浪费。

下面的例子展示了一种办法,使得子表上建立的索引即可用于防止锁表又可用于查询,代价就是父表上会多一个索引。但考虑到父表通常比较小,而且子表还不止一个,这个代价还是值得的。


DROP TABLE test_child;
DROP TABLE test_parent;

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

----- 下面会在(parent_id,delete_flag)上建立唯一索引。虽然主键上已经有了索引,但ORACLE不允许我们重复使用那个索引来做唯一约束。
ALTER TABLE test_parent ADD CONSTRAINT test_parent_uk UNIQUE (parent_id,delete_flag);

主表测试数据:
INSERT INTO test_parent(parent_id,deleted_date) VALUES (1,NULL);
INSERT INTO test_parent(parent_id,deleted_date) VALUES (2,DATE '2009-1-1');
INSERT INTO test_parent(parent_id,deleted_date) VALUES (3,DATE '2010-1-1');


创建一个函数判断子表数据创建日期是否在删除之前,如果是则返回标记'N', 否则返回'Y'使得引用出错:
CREATE OR REPLACE FUNCTION f_delete_flag(p_parent_id IN NUMBER,p_child_created_date DATE)
RETURN VARCHAR2 DETERMINISTIC
AS
   lv_deleted_date DATE;
BEGIN
   IF p_parent_id IS NULL THEN
      RETURN NULL;
   END IF;
   
   SELECT deleted_date INTO lv_deleted_date FROM test_parent WHERE parent_id = p_parent_id;
   RETURN CASE WHEN p_child_created_date>lv_deleted_date THEN 'Y' ELSE 'N' END;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
        RETURN 'Y';
END f_delete_flag;
/

CREATE TABLE test_child (
       child_id      NUMBER(10)  PRIMARY KEY
      ,parent_id     NUMBER(10)
      ,created_date  DATE
      ,delete_flag  AS (CAST(f_delete_flag(parent_id,created_date) AS VARCHAR2(1)))
       );

ALTER TABLE test_child ADD CONSTRAINT test_child_fk FOREIGN KEY (parent_id,delete_flag) REFERENCES test_parent(parent_id,delete_flag);

---- 建立一个索引供父表删改主键用,同时这个索引也可用于查询:
CREATE INDEX test_child_idx ON test_child(parent_id,delete_flag);


子表测试的结果和前例一样:

jsu@JSU> INSERT INTO test_child(child_id,parent_id,created_date) VALUES (1,1,SYSDATE-1);

1 row created.

jsu@JSU> INSERT INTO test_child(child_id,parent_id,created_date) VALUES (2,1,SYSDATE);

1 row created.

jsu@JSU> INSERT INTO test_child(child_id,parent_id,created_date) VALUES (3,2,SYSDATE-1);
INSERT INTO test_child(child_id,parent_id,created_date) VALUES (3,2,SYSDATE-1)
*
ERROR at line 1:   ------ 这是因为created_date 在删除日期之后
ORA-02291: integrity constraint (JSU.TEST_CHILD_FK) violated - parent key not found

jsu@JSU> INSERT INTO test_child(child_id,parent_id,created_date) VALUES (3,2,DATE '2007-10-1');

1 row created.     ----- 改为删除日期之前则顺利插入


jsu@JSU> UPDATE test_parent SET deleted_date = DATE '2010-6-1' WHERE parent_id=1;

1 row updated.

jsu@JSU> UPDATE test_child SET created_date = created_date WHERE child_id=1;
UPDATE test_child SET created_date = created_date WHERE child_id=1
*
ERROR at line 1: ------- 修改子表会触发校验
ORA-02291: integrity constraint (JSU.TEST_CHILD_FK) violated - parent key not found

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表