ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 小议Oracle外键约束修改行为(七)

小议Oracle外键约束修改行为(七)

原创 Linux操作系统 作者:yangtingkun 时间:2009-02-13 22:05:02 0 删除 编辑

Oracle的外键用来限制子表中参考的字段的值,必须在主表中存在。而且在主表的记录发生变化导致外键参考唯一约束值发生了变化时,定义了一系列的动作。

这篇描述一下如何实现RESTRICT

小议Oracle外键约束修改行为(一):http://yangtingkun.itpub.net/post/468/478045

小议Oracle外键约束修改行为(二):http://yangtingkun.itpub.net/post/468/478119

小议Oracle外键约束修改行为(三):http://yangtingkun.itpub.net/post/468/478172

小议Oracle外键约束修改行为(四):http://yangtingkun.itpub.net/post/468/478280

小议Oracle外键约束修改行为(五):http://yangtingkun.itpub.net/post/468/478374

小议Oracle外键约束修改行为(六):http://yangtingkun.itpub.net/post/468/478437

 

 

前面几篇文章介绍了Oracle所支持的3种约束行为NO ACTIONDELETE SET NULLDELETE CASCADE

至于SQL标准中定义的其他操作,Oracle只能通过触发器来实现,这里给出一个简单的RESTRICT操作的例子。

SQL> DROP TABLE T_C;

表已删除。

SQL> DROP TABLE T_P;

表已删除。

SQL> CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> ALTER TABLE T_P ADD PRIMARY KEY (ID);

表已更改。

SQL> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> ALTER TABLE T_C ADD CONSTRAINT FK_T_C
  2  FOREIGN KEY (FID)
  3  REFERENCES T_P (ID);

表已更改。

SQL> INSERT INTO T_P VALUES (1, 'A');

已创建 1 行。

SQL> INSERT INTO T_P VALUES (2, 'B');

已创建 1 行。

SQL> INSERT INTO T_C VALUES (1, 1, 'A');

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL标准定义的RESTRICT操作其实和NO ACTION操作十分类似。不同之处在于,RESTRICT的检查在语句执行之前,一旦发现主键被引用,就会报错,阻止更新或删除操作的执行。

SQL> SELECT * FROM T_P;

        ID NAME
---------- ------------------------------
         1 A
         2 B

SQL> SELECT * FROM T_C;

        ID        FID NAME
---------- ---------- ------------------------------
         1          1 A

SQL> UPDATE T_P SET ID = 3 WHERE ID = 1;
UPDATE T_P SET ID = 3 WHERE ID = 1
*
1 行出现错误:
ORA-02292:
违反完整约束条件 (YANGTK.FK_T_C) - 已找到子记录日志

对于上面这种情况,无论是NO ACTION还是RESTRICT,效果没有什么差别,而对于下面的情况就不一样了:

SQL> UPDATE T_P SET ID = ID - 1;

已更新2行。

SQL> SELECT * FROM T_P;

        ID NAME
---------- ------------------------------
         0 A
         1 B

SQL> UPDATE T_P SET ID = ID + 1;

已更新2行。

SQL> SELECT * FROM T_P;

        ID NAME
---------- ------------------------------
         1 A
         2 B

对于NO ACTION来说,约束的检查发生在语句之后,所有上面的语句可以顺利执行,而对于RESTRICT而言,这个操作会直接报错。

下面构建一个RESTRICT操作的触发器:

SQL> CREATE OR REPLACE TRIGGER T_P_RESTRICT
  2  BEFORE DELETE OR UPDATE OF ID ON T_P
  3  FOR EACH ROW
  4  WHEN (NVL(NEW.ID, -1) != NVL(OLD.ID, -1))
  5  DECLARE
  6   V_STR VARCHAR2(32767);
  7   V_COUNT NUMBER;
  8  BEGIN
  9   FOR I IN (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME
 10    FROM ALL_CONSTRAINTS
 11    WHERE CONSTRAINT_TYPE = 'R'
 12    AND R_OWNER = 'YANGTK'
 13    AND R_CONSTRAINT_NAME IN
 14     (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'T_P'))
 15   LOOP
 16    V_STR := 'SELECT COUNT(*) FROM  ' || I.TABLE_NAME || ' WHERE '; 
 17    FOR J IN (SELECT COLUMN_NAME FROM ALL_CONS_COLUMNS
 18     WHERE WNER = I.OWNER
 19     AND TABLE_NAME = I.TABLE_NAME
 20     AND CONSTRAINT_NAME = I.CONSTRAINT_NAME)
 21    LOOP
 22     V_STR := V_STR || J.COLUMN_NAME || ' = ' || :OLD.ID;
 23    END LOOP;
 24    EXECUTE IMMEDIATE V_STR INTO V_COUNT;
 25    IF V_COUNT > 0 THEN
 26     RAISE_APPLICATION_ERROR(-20001, '
违反完整约束条件限制操作 - 已找到子记录日志');
 27    END IF;
 28   END LOOP;
 29  END;
 30  /

触发器已创建

SQL> SELECT * FROM T_P;

        ID NAME
---------- ------------------------------
         1 A
         2 B

SQL> SELECT * FROM T_C;

        ID        FID NAME
---------- ---------- ------------------------------
         1          1 A

SQL> UPDATE T_P SET ID = ID - 1;
UPDATE T_P SET ID = ID - 1
                   *
1 行出现错误:
ORA-20001:
违反完整约束条件限制操作 - 已找到子记录日志
ORA-06512:
"YANGTK.T_P_RESTRICT", line 22
ORA-04088:
触发器 'YANGTK.T_P_RESTRICT' 执行过程中出错

这种方式可以实现RESTRICT操作,但是对于其他会话的外键的引用,RESTRICT操作是看不到的:

SQL> CONN YANGTK/YANGTK@YTK92
已连接。
SQL> SET SQLP 'SQL2> '
SQL2> INSERT INTO T_C VALUES (2, 2, 'B');

已创建 1 行。

在另一个会话插入一条参考主表ID2的记录,且不提交,这时在主表更新或删除ID2的记录时,会被锁住,RESTRICT操作不会起作用,因为这时看不到其他用户未提交的修改:

SQL> DELETE T_P WHERE ID = 2;

而如果在第二个会话中提交事务:

SQL2> COMMIT;

提交完成。

RESTRICT操作会起作用,因为这时已经提交,而当前的会话是可以看到其他会话中已提交数据的:

DELETE T_P WHERE ID = 2
                   *
1 行出现错误:
ORA-20001:
违反完整约束条件限制操作 - 已找到子记录日志
ORA-06512:
"YANGTK.T_P_RESTRICT", line 22
ORA-04088:
触发器 'YANGTK.T_P_RESTRICT' 执行过程中出错


SQL>

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-551864/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10441933