小议Oracle外键约束修改行为

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






在SQL92标准中定义了几种外键改变后，如何处理子表记录的动作，其中包括：


限制Restrict：这种方式不允许对被参考的记录的键值执行更新或删除的操作；


置为空Set to null：当参考的数据被更新或者删除，那么所有参考它的外键值被置为空；


置为默认值Set to default：当参考的数据被更新或者删除，那么所有参考它的外键值被置为一个默认值；


级联Cascade：当参考的数据被更新，则参考它的值同样被更新，当参考的数据被删除，则参考它的子表记录也被删除；


不做操作No action：这种方式不允许更新或删除被参考的数据。和限制方式的区别在于，这种方式的检查发生在语句执行之后。Oracle默认才会的方式就是这种方式。


Oracle明确支持的方式包括No action、Set to null和Cascade。对于Set to Default和Restrict，Oracle的约束类型并不直接支持，不过可以通过触发器来实现。


简单看一下Oracle的默认处理方式No action：


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;


提交完成。


对于No Action操作而言，如果主键的记录被外键所参考，那么主键记录是无法更新或删除的。


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




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> DELETE T_P WHERE ID = 2;


已删除 1 行。


不过No Action又和Restrict操作有所区别，No Action允许用户执行语句，在语句执行之后，或者事务结束的时候才会检查是否违反约束。而Restrict只有检测到有外键参考主表的记录，就不允许删除和更新的操作执行了。


这也使得No Action操作支持延迟约束：


SQL> ALTER TABLE T_C DROP CONSTRAINT FK_T_C;


表已更改。


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


表已更改。


SQL> SELECT * FROM T_P;


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


SQL> SELECT * FROM T_C;


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


SQL> DELETE T_P WHERE ID = 1;


已删除 1 行。


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


已创建 1 行。


SQL> COMMIT;


提交完成。


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


这篇简单描述一下SET TO NULL操作。


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






上一篇描述了Oracle外键处理默认操作：No Action，这里简单介绍一下SET TO NULL操作。还是利用前面例子的表，不过约束需要重建。


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)
4 ON DELETE SET NULL;


表已更改。


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> INSERT INTO T_C VALUES (2, 2, 'B');


已创建 1 行。


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


已创建 1 行。


SQL> COMMIT;


提交完成。


下面检查一下DELETE SET NULL是如何工作的：


SQL> SELECT * FROM T_P;


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


SQL> SELECT * FROM T_C;


ID FID NAME
---------- ---------- ------------------------------
1 1 A
2 2 B
3 1 C


SQL> DELETE T_P WHERE ID = 2;


已删除 1 行。


SQL> SELECT * FROM T_C;


ID FID NAME
---------- ---------- ------------------------------
1 1 A
2 B
3 1 C


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


可以看到这个Set no null的操作和语法中的名称一样，只对DELETE操作有效，而对于UPDATE操作无效。


这个约束操作还有一个前提，就是要求子表的外键列允许为空，否则对主表的DELETE操作会报错：


SQL> SELECT * FROM T_C;


ID FID NAME
---------- ---------- ------------------------------
1 1 A
2 B
3 1 C


SQL> DELETE T_C WHERE ID = 2;


已删除 1 行。


SQL> ALTER TABLE T_C MODIFY FID NOT NULL;


表已更改。


SQL> DELETE T_P;
DELETE T_P
*第 1 行出现错误:
ORA-01407: 无法更新 ("YANGTK"."T_C"."FID") 为 NULL


从这里也可以看到，虽然Oracle支持Set to null，但是只是实现了DELETE语句，而没有实现UPDATE语句，这和SQL标准的定义还是有区别的。


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


这篇简单描述一下CASCADE操作。


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


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






上一篇描述了Oracle外键处理操作：SET TO NULL，这里简单介绍一下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)
4 ON DELETE CASCADE;


表已更改。


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> INSERT INTO T_C VALUES (2, 2, 'B');


已创建 1 行。


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


已创建 1 行。


SQL> COMMIT;


提交完成。


下面看看CASCADE是如何工作的：


SQL> SELECT * FROM T_P;


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


SQL> SELECT * FROM T_C;


ID FID NAME
---------- ---------- ------------------------------
1 1 A
2 2 B
3 1 C


SQL> DELETE T_P WHERE ID = 2;


已删除 1 行。


SQL> SELECT * FROM T_C;


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


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


可以看到，和SET TO NULL约束一样，CASCADE操作也是只对DELETE语句有效，而对于UPDATE语句不起作用。


另外提一句，和SET TO NULL不同，CASCADE具有传递性，主表删除一条记录，会导致子表参考的记录被删除，而子表的记录被删除又会导致子表的子表记录被删除：


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


表已更改。


SQL> CREATE TABLE T_C_C (ID NUMBER PRIMARY KEY, FID NUMBER, 
2 CONSTRAINT FK_T_C_C FOREIGN KEY (FID) REFERENCES T_C(ID) ON DELETE CASCADE);


表已创建。


SQL> INSERT INTO T_C_C VALUES (1, 1);


已创建 1 行。


SQL> CREATE TABLE T_C_C_C (ID NUMBER PRIMARY KEY, FID NUMBER, 
2 CONSTRAINT FK_T_C_C_C FOREIGN KEY (FID) REFERENCES T_C_C(ID) ON DELETE CASCADE);


表已创建。


SQL> INSERT INTO T_C_C_C VALUES (1, 1);


已创建 1 行。


SQL> COMMIT;


提交完成。


下面看看级联删除的效果：


SQL> SELECT * FROM T_P;


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


SQL> SELECT * FROM T_C;


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


SQL> SELECT * FROM T_C_C;


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


SQL> SELECT * FROM T_C_C_C;


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


SQL> DELETE T_P;


已删除 1 行。


SQL> SELECT * FROM T_P;


未选定行


SQL> SELECT * FROM T_C;


未选定行


SQL> SELECT * FROM T_C_C;


未选定行


SQL> SELECT * FROM T_C_C_C;


未选定行


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


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


小议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所支持的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 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 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;
27 /


触发器已创建


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


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


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


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


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


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_NULL 
2 BEFORE 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 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 || ' = NULL '
22 || 'WHERE ' || J.COLUMN_NAME || ' = ' || :OLD.ID;
23 END LOOP;
24 EXECUTE IMMEDIATE V_STR;
25 END LOOP;
26 END;
27 /


触发器已创建


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 A


UPDATE SET NULL操作其实和UPDATE CASCADE很相似，不同之处无非是一个置为NULL，另一个置为主键的新值。


小议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


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


上一篇 / 下一篇  2009-02-13 22:05:02 / 个人分类：ORACLE
查看( 131 ) / 评论( 0 ) / 评分( 0 / 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 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>