-- 创建测试主表. 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');
SQL> -- 创建外键(默认选项)
SQL> alter table test_sub add constraint main_id_cons foreign key (main_id) references test_main;
Table altered.
SQL>
SQL> -- 测试删除主表数据. 将出错 ORA-02292:违反完整约束条件
SQL> DELETE
2 test_main
3 WHERE
4 ID = 1;
DELETE
*
ERROR at line 1:
ORA-02292: integrity constraint(HR.MAIN_ID_CONS) violated - child record found
测试完毕后，删除外键约束
alter table test_subDROP constraint main_id_cons;
SQL> -- 创建外键(使用 ON DELETECASCADE 选项，删除主表的时候，同时删除子表)
SQL> alter table test_sub add constraint main_id_cons foreign key (main_id) references test_main on delete cascade;
Table altered.
SQL>
SQL> -- 测试删除主表数据. 将成功地执行.
SQL> DELETE TEST_MAIN WHERE ID = 1;
1 row deleted.
SQL>
SQL> -- 测试检索子表，应该只有一条 main_id = 2 的数据.
SQL> SELECT * FROM test_sub;
ID MAIN_ID VALUE
---------- ---------- --------------------
2 2 TWOTWO
测试完毕后，删除外键约束
alter table test_sub drop constraint main_id_cons ;
SQL> -- 创建外键(使用 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>
SQL> -- 测试删除主表数据. 将成功地执行.
SQL> DELETE TEST_MAIN WHERE ID = 2;
1 row deleted.
SQL>
SQL> -- 测试检索子表，应该有一条的 main_id = null
SQL> SELECT * FROM test_sub;
ID MAIN_ID VALUE
---------- ---------- --------------------
2 TWOTWO
测试完毕后，删除外键约束
alter table test_sub drop constraint main_id_cons;
启用/禁用
当遇到批量数据导入的时候，如果外键处于有效的状态。
那么要求 数据导入的程序， 必须先导入主表的数据，然后再导入子表的数据。
如果表多，且关系复杂的话，会增加很大的工作量。
可以在数据导入以前， 先暂时禁用外键约束。
子表、主表数据都导入完毕后， 再启用外键约束。
以下测试环境为 初始的创建表、初始数据的情况。
SQL> -- 创建外键(默认选项)
SQL> alter table test_sub addconstraint 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;
提交完成。
------
2013年4月14日补充:
自关联 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
2 ADD CONSTRAINT fk_test_self
3 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;
已删除 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
------
2013年4月24日补充:
批量管理
在正常运行的系统中。 某些非常关键的核心表。 会带有几十个外键关联的。 例如一个商业系统的 “店铺表” 所有的各种资产、人员、设备、交易数据 等，都与这个 店铺表建立外键关联了。 一旦遇到 “店铺编码规则需要变更” 手动去一个一个地禁用外键关联， 成本也太高了。 这种情况下 需要通过 执行一个 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 = '主表';
597
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
