oracle外键改为可用,小议Oracle外键约束修改行为（七）

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

这篇描述一下如何实现RESTRICT。

前面几篇文章介绍了Oracle所支持的3种约束行为NO ACTION、DELETE SET NULL和DELETE 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行。

在另一个会话插入一条参考主表ID为2的记录，且不提交，这时在主表更新或删除ID为2的记录时，会被锁住，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>