Oracle 外键约束

本文讨论一下Oracle中的外键约束问题，以及和DB2中的小不同。首先创建测试环境。

-- 创建测试主表. ID 是主键.

CREATE TABLE test_main (

 id      INT,

 value   VARCHAR(10),

 PRIMARY KEY(id)  

);

-- 创建测试子表. 

CREATE TABLE test_sub (

  id      INT,

 main_id INT,

 value   VARCHAR(10),

 PRIMARY KEY(id)  

);

-- 插入测试主表数据.

INSERT INTO test_main(id, value) VALUES (1,'ONE');

INSERT INTO test_main(id, value) VALUES (2,'TWO');

-- 插入测试子表数据.

INSERT INTO test_sub(id, main_id, value)VALUES (1, 1, 'ONEONE');

INSERT INTO test_sub(id, main_id, value)VALUES (2, 2, 'TWOTWO');

默认外键约束方式

-- 创建外键(默认选项) ---默认选项实质就是DB2中的 NO ACTION和 RESTRICT模式（DB2很奇怪，这两个功能好象是一样的。）

SQL> ALTER TABLE test_sub ADD CONSTRAINT main_id_cons FOREIGN KEY (main_id) REFERENCES test_main;

Table altered.

-- 测试删除主表数据. 将出错 ORA-02292:违反完整约束条件

SQL-> DELETE  test_main WHERE ID = 1;

ERROR at line 1:

ORA-02292: integrity constraint(HR.MAIN_ID_CONS) violated - child record found

测试完毕后，删除外键约束

ALTER TABLE test_subDROP CONSTRAINT main_id_cons;

DELETE CASCADE 方式，这个等同于DB2中的CASCADE选项。

 -- 创建外键(使用 ON DELETECASCADE 选项，删除主表的时候，同时删除子表)

SQL> ALTER TABLE test_sub

ADDCONSTRAINT main_id_cons

 FOREIGN KEY (main_id) REFERENCES  test_main  ON DELETE  CASCADE;

Table altered.

-- 测试删除主表数据. 将成功地执行.

SQL> DELETE TEST_MAIN  WHERE  ID = 1;

1 row deleted.

测试完毕后，删除外键约束

ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;

SET NULL方式，这个也等同于DB2中的SET NULL选项。

--创建外键(使用 ON DELETESET NULL 选项，删除主表的时候，同时将子表的 main_id 设置为 NULL)

SQL> ALTER TABLE test_sub

 ADD CONSTRAINT main_id_cons

 FOREIGN KEY (main_id) REFERENCES  test_main  ON DELETE SET NULL;

Table altered.

启用/禁用

当遇到批量数据导入的时候，如果外键处于有效的状态。
那么要求数据导入的程序，必须先导入主表的数据，然后再导入子表的数据。
如果表多，且关系复杂的话，会增加很大的工作量。

可以在数据导入以前，先暂时禁用外键约束。
子表、主表数据都导入完毕后，再启用外键约束。

以下测试环境为初始的创建表、初始数据的情况。

SQL> -- 创建外键(默认选项)
SQL> ALTER TABLE test_sub ADD CONSTRAINT main_id_cons FOREIGN KEY (main_id) REFERENCES test_main;

表已更改。

SQL> -- 测试插入子表数据. 将出错 ORA-02291: 违反完整约束条件
SQL> INSERT INTO test_sub (ID, MAIN_ID, VALUE) VALUES (3, 3, 'THREETHREE');
INSERT INTO test_sub (ID, MAIN_ID, VALUE) VALUES (3, 3, 'THREETHREE')
*
ERROR 位于第 1 行:
ORA-02291: 违反完整约束条件 (TEST.MAIN_ID_CONS) - 未找到父项关键字

-- 尝试禁用外键约束.
SQL> ALTER TABLE test_sub MODIFY CONSTRAINT main_id_cons DISABLE;
表已更改。

SQL> INSERT INTO test_sub (ID, MAIN_ID, VALUE) VALUES (3, 3, 'THREETHREE');
已创建 1 行。

SQL> INSERT INTO test_main(id, value) VALUES (3, 'THREE');
已创建 1 行。

SQL> commit;
提交完成。

-- 恢复启用外键约束.
SQL> ALTER TABLE test_sub MODIFY CONSTRAINT main_id_cons ENABLE;
表已更改。

SQL> DELETE test_main WHERE ID = 1;
DELETE test_main WHERE ID = 1
*
ERROR 位于第 1 行:
ORA-02292: 违反完整约束条件 (TEST.MAIN_ID_CONS) - 已找到子记录

测试完毕后，删除外键约束

ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;

延迟约束

在编程开发环境中，可能会遇到，要先插入子表，然后插入主表。
或者先更新主表的主键，然后更新子表的外键的情况。

由于是开发环境下，因此不适合使用先 DISABLE 再 ENABLE 的处理机制。
原因是程序中可能会存在并发处理

也就是

用户A DISABLE -- 更新处理 -- ENABLE
用户B DISABLE -- 更新处理 -- ENABLE

这种情况下，需要启用延迟约束
也就是 INSERT / UPDATE 语句执行的时候，暂时不检查数据的完整性，等到 Commit 的时候，统一做检查。

SQL> -- 创建外键(默认选项)
SQL> ALTER TABLE test_sub ADD CONSTRAINT main_id_cons
2 FOREIGN KEY (main_id) REFERENCES test_main
3 DEFERRABLE INITIALLY DEFERRED;
表已更改。

SQL> INSERT INTO test_sub (ID, MAIN_ID, VALUE) VALUES (4, 4, 'FOURFOUR');

已创建 1 行。

SQL> commit;
commit
*
ERROR 位于第 1 行:
ORA-02091: 事务处理已回退
ORA-02291: 违反完整约束条件 (TEST.MAIN_ID_CONS) - 未找到父项关键字

SQL> INSERT INTO test_sub (ID, MAIN_ID, VALUE) VALUES (4, 4, 'FOURFOUR');
已创建 1 行。

SQL> INSERT INTO test_main(id, value) VALUES (4, 'FOUR');
已创建 1 行。

SQL> commit;
提交完成。

自关联 DELETE CASCADE

-- 创建自关联测试表. ID 是主键.  pID 是父节点
CREATE TABLE test_self (
  id    INT   NOT NULL,
  pid   INT,
  value   VARCHAR(10),
  PRIMARY KEY(id)  
);


INSERT INTO test_self VALUES(1, NULL,   '.NET');
INSERT INTO test_self VALUES(2, 1,      'C#');
INSERT INTO test_self VALUES(3, 1,      'J#');
INSERT INTO test_self VALUES(4, 1,      'ASP.NET');
INSERT INTO test_self VALUES(5, 1,      'VB.NET');

INSERT INTO test_self VALUES(6, NULL,   'J2EE');
INSERT INTO test_self VALUES(7, 6,      'EJB');
INSERT INTO test_self VALUES(8, 6,      'Servlet');
INSERT INTO test_self VALUES(9, 6,      'JSP');

INSERT INTO test_self VALUES(10, NULL,  'Database');
INSERT INTO test_self VALUES(11, 10,    'DB2');
INSERT INTO test_self VALUES(12, 10,    'MySQL');
INSERT INTO test_self VALUES(13, 10,    'Oracle');
INSERT INTO test_self VALUES(14, 10,    'SQL Server');


INSERT INTO test_self VALUES(15, 13,    'PL/SQL');
INSERT INTO test_self VALUES(16, 15,    'Function');
INSERT INTO test_self VALUES(17, 15,    'Procedure');
INSERT INTO test_self VALUES(18, 15,    'Package');
INSERT INTO test_self VALUES(19, 15,    'Cursor');

INSERT INTO test_self VALUES(20, 14,    'T-SQL');

自关联添加外间

SQL> ALTER TABLE test_self 
ADD CONSTRAINT fk_test_self FOREIGN KEY (pid) 
REFERENCES test_self(id) ON DELETE CASCADE;

表已更改。

-- 测试级联删除层次为 1层的. (预期结果是 id=1,2,3,4,5 的被删除. )

SQL> DELETE FROM test_self WHERE ID = 1;

-- 数据核对.

SQL> select * from test_self;

ID PID VALUE

---------- ---------- --------------------

6 J2EE

7 6 EJB

8 6 Servlet

9 6 JSP

10 Database

11 10 DB2

12 10 MySQL

13 10 Oracle

14 10 SQL Server

15 13 PL/SQL

16 15 Function

ID PID VALUE

---------- ---------- --------------------

17 15 Procedure

18 15 Package

19 15 Cursor

20 14 T-SQL

已选择15行。

-- 测试级联删除层次为多层的. (预期结果是 id=10 与 10以后的被删除. )

SQL> DELETE FROM test_self WHERE ID = 10;

已删除 1 行。

SQL> select * from test_self;

ID PID VALUE

---------- ---------- --------------------

6 J2EE

7 6 EJB

8 6 Servlet

9 6 JSP

批量管理

在正常运行的系统中。
某些非常关键的核心表。
会带有几十个外键关联的。

例如一个商业系统的 “店铺表”
所有的各种资产、人员、设备、交易数据 等，都与这个 店铺表建立外键关联了。


一旦遇到 “店铺编码规则需要变更”
手动去一个一个地禁用外键关联， 成本也太高了。

这种情况下
需要通过 执行一个 SQL 语句， 查询出 一组 sql 语句的结果。
然后去执行这些 sql 语句即可。

-- 下面的 SQL 语句
-- 是把所有的 外键引用 主表 的外键，当前状态是可用的， 都 禁用掉。
SELECT
  'ALTER TABLE ' || USER_CONSTRAINTS.TABLE_NAME
    || ' MODIFY CONSTRAINT ' ||  USER_CONSTRAINTS.CONSTRAINT_NAME ||  '  DISABLE; ' AS DisableSQL
FROM
  USER_CONSTRAINTS
    JOIN USER_INDEXES
    ON (USER_CONSTRAINTS.R_CONSTRAINT_NAME
        = USER_INDEXES.INDEX_NAME)
WHERE
  USER_CONSTRAINTS.CONSTRAINT_TYPE = 'R'
  AND USER_CONSTRAINTS.STATUS = 'ENABLED'
  AND USER_INDEXES.TABLE_NAME = '主表';

-- 恢复的处理也基本一样。
SELECT
  'ALTER TABLE ' || USER_CONSTRAINTS.TABLE_NAME
    || ' MODIFY CONSTRAINT ' ||  USER_CONSTRAINTS.CONSTRAINT_NAME ||  '  ENABLE; ' AS EnableSQL
FROM
  USER_CONSTRAINTS
    JOIN USER_INDEXES
    ON (USER_CONSTRAINTS.R_CONSTRAINT_NAME
        = USER_INDEXES.INDEX_NAME)
WHERE
  USER_CONSTRAINTS.CONSTRAINT_TYPE = 'R'
  AND USER_CONSTRAINTS.STATUS = 'DISABLE'
  AND USER_INDEXES.TABLE_NAME = '主表';

-- 为了避免某些外键原来就禁用的， 一来一回处理以后， 变可用了。
-- 可以先 产生一个 恢复用的 SQL.  全部禁用以后， 相关更新处理完毕以后,  最后用下面这个语句恢复。
SELECT
  'ALTER TABLE  ' || USER_CONSTRAINTS.TABLE_NAME
    || ' MODIFY CONSTRAINT  '
	||  USER_CONSTRAINTS.CONSTRAINT_NAME
	||  '  '
	||  USER_CONSTRAINTS.STATUS || ' ; ' AS BackupSQL
FROM
  USER_CONSTRAINTS
    JOIN USER_INDEXES
    ON (USER_CONSTRAINTS.R_CONSTRAINT_NAME
        = USER_INDEXES.INDEX_NAME)
WHERE
  USER_CONSTRAINTS.CONSTRAINT_TYPE = 'R'
  AND USER_INDEXES.TABLE_NAME = '主表';

posted @ 2013-07-20 19:43 胡.杰阅读(952) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 零经验选手，Compose 一天开发一款小游戏！
· 因为Apifox不支持离线，我果断选择了Apipost！
· 通过 API 将Deepseek响应流式内容输出到前端

Oracle 外键约束

公告

常用链接

随笔分类 (323)

随笔档案 (327)

相册 (4)

阅读排行榜

评论排行榜

推荐排行榜

最新评论