当前位置导航:炫浪网>>网络学院>>编程开发>>Oracle教程

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

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

    这篇描述一下如何实现UPDATE CASCADE.

    前面几篇文章介绍了Oracle所支持的3种约束行为NO ACTION、DELETE SET NULL和DELETE CASCADE.

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

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_UPDATE_CASCADE
2 BEFORE UPDATE ON T_P
3 FOR EACH ROW
4 DECLARE
5 V_STR VARCHAR2(32767);
6 BEGIN
7 IF NVL(:NEW.ID, -1) != NVL(:OLD.ID, -1) THEN
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 COLUMN_NAME FROM ALL_CONS_COLUMNS
17 WHERE OWNER = I.OWNER
18 AND TABLE_NAME = I.TABLE_NAME
19 AND CONSTRAINT_NAME = I.CONSTRAINT_NAME)
20 LOOP
21 V_STR := V_STR || J.COLUMN_NAME || ' = ' || :NEW.ID
22 || ' WHERE ' || J.COLUMN_NAME || ' = ' || :OLD.ID;
23 END LOOP;
24 EXECUTE IMMEDIATE V_STR;
25 END LOOP;
26 END IF;
27 END;
28 /

触发器已创建

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 3 A

采用动态SQL的好处是,即使新增了新的外键表,触发器也不用重新编辑:

SQL> CREATE TABLE T_C1 (ID NUMBER, FID NUMBER, CONSTRAINT FK_T_C1
2 FOREIGN KEY (FID) REFERENCES T_P);

表已创建。

SQL> INSERT INTO T_C1 VALUES (1, 3);

已创建 1 行。

SQL> UPDATE T_P SET ID = 1 WHERE ID = 3;

已更新 1 行。

SQL> SELECT * FROM T_P;

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

SQL> SELECT * FROM T_C;

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

SQL> SELECT * FROM T_C1;

ID FID
---------- ----------
1 1

相关内容
赞助商链接