小议Oracle外键约束修改行为（六）

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

这篇描述一下如何实现SET DEFAULT。

小议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所支持的3种约束行为NO ACTION、DELETE SET NULL和DELETE CASCADE。

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

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> 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) - 已找到子记录日志

下面构造一个触发器：

SQL> CREATE OR REPLACE TRIGGER T_P_SET_DEFAULT
  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  BEGIN
  8   FOR I IN (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME
  9    FROM ALL_CONSTRAINTS
 10    WHERE CONSTRAINT_TYPE = 'R'
 11    AND R_OWNER = 'YANGTK'
 12    AND R_CONSTRAINT_NAME IN
 13     (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'T_P'))
 14   LOOP
 15    V_STR := 'UPDATE ' || I.TABLE_NAME || ' SET '; 
 16    FOR J IN (SELECT A.COLUMN_NAME, B.DATA_DEFAULT
 17     FROM ALL_CONS_COLUMNS A, ALL_TAB_COLUMNS B
 18     WHERE A.OWNER = I.OWNER
 19     AND B.OWNER = I.OWNER
 20     AND A.TABLE_NAME = I.TABLE_NAME
 21     AND B.TABLE_NAME = I.TABLE_NAME
 22     AND A.COLUMN_NAME = B.COLUMN_NAME
 23     AND CONSTRAINT_NAME = I.CONSTRAINT_NAME)
 24    LOOP
 25     V_STR := V_STR || J.COLUMN_NAME || ' = ' || J.DATA_DEFAULT
 26      || ' WHERE ' || J.COLUMN_NAME || ' = ' || :OLD.ID;
 27    END LOOP;
 28    EXECUTE IMMEDIATE V_STR;
 29   END LOOP;
 30  END;
 31  /

触发器已创建

SQL> ALTER TABLE T_C MODIFY FID DEFAULT 2;

表已更改。

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;

已更新 1 行。

SQL> SELECT * FROM T_P;

        ID NAME
---------- ------------------------------
         3 A
         2 B

SQL> SELECT * FROM T_C;

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

下面检查一下DELETE操作是否有效：

SQL> UPDATE T_C SET FID = 3;

已更新 1 行。

SQL> SELECT * FROM T_C;

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

SQL> DELETE T_P WHERE ID = 3;

已删除 1 行。

SQL> SELECT * FROM T_P;

        ID NAME
---------- ------------------------------
         2 B

SQL> SELECT * FROM T_C;

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

 

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

转载于:http://blog.itpub.net/4227/viewspace-548760/