oracle 禁用/启用引用的FK

x3naxklr  于 2023-05-16  发布在  Oracle
关注(0)|答案(1)|浏览(139)

我正试图自动化一个过程,以禁用和启用外键,这样我就可以删除“坏数据”。
下面是我们的一个应用程序模式的示例。我发现这个Tom Kyte查询可以显示一些表之间的关系。

  1. create table c ( x NUMBER,
  2. CONSTRAINT c_pk PRIMARY KEY(x)
  3. );
  4. create table c1 ( x NUMBER,
  5. CONSTRAINT c1_pk PRIMARY KEY(x),
  6. CONSTRAINT c1_fk FOREIGN KEY (x) REFERENCES c(x));
  7. create table c2 ( x NUMBER,
  8. CONSTRAINT c2_pk PRIMARY KEY(x),
  9. CONSTRAINT c2_fk FOREIGN KEY (x) REFERENCES c2(x));
  10. create table c3 ( x NUMBER,
  11. CONSTRAINT c3_pk PRIMARY KEY(x),
  12. CONSTRAINT c3_fk FOREIGN KEY (x) REFERENCES c2(x));
  13. create table c4 ( x NUMBER,
  14. CONSTRAINT c4_pk PRIMARY KEY(x),
  15. CONSTRAINT c4_fk FOREIGN KEY (x) REFERENCES c2(x));
  16. /* child/parent relationship */
  17. with cte as (
  18. select table_name, constraint_type, constraint_name, r_constraint_name,
  19. max(decode(constraint_type,'R',1,0)) over(partition by table_name) is_r
  20. from user_constraints
  21. where constraint_type in ('P', 'U', 'R')
  22. )
  23. , child_parent as (
  24. select distinct s.table_name child, d.table_name parent, d.constraint_type
  25. from (select * from cte where constraint_type = 'R' or is_r = 0) s
  26. left join cte d
  27. on s.r_constraint_name = d.constraint_name
  28. and s.table_name != d.table_name
  29. )
  30. select level lvl, child, parent, constraint_type
  31. from child_parent
  32. start with parent is null
  33. connect by parent = prior child
  34. order siblings by parent, child;
  35. LVL CHILD PARENT CONSTRAINT_TYPE
  36. 1 C - -
  37. 2 C1 C P
  38. 1 C2 - -
  39. 2 C3 C2 P
  40. 2 C4 C2 P

我有以下代码,这将禁用和启用FK的。我知道这可以合并成一段代码(过程,它接受参数???)但我还没有达到这一点。
正如你从下面的输出中看到的,它禁用和启用了模式中的所有FK,我只需要代码对一组引用执行此操作。
例如,如果我传入表“C”,我的代码应该只禁用和启用FK C1_FK。如果传入表“C2”,则代码应仅禁用和启用C3_FK、C4_FK
任何帮助将不胜感激。提前感谢所有回答的人。注意,如果有更好的方法来编码这个解决方案,我愿意接受所有的建议

  1. /* disable foreign keys */
  2. DECLARE
  3. sql_command varchar2(500);
  4. BEGIN
  5. FOR r IN (
  6. SELECT '"' || a.owner
  7. || '"."'
  8. || a.table_name
  9. || '"' AS full_table_name,
  10. a.constraint_name
  11. FROM user_constraints a
  12. JOIN user_constraints b
  13. ON ( a.r_constraint_name = b.constraint_name
  14. AND a.r_owner = b.owner )
  15. WHERE a.constraint_type = 'R'
  16. AND a.status = 'ENABLED'
  17. ORDER BY 1 )
  18. LOOP
  19. sql_command := 'alter table ' || r.full_table_name || ' disable constraint ' || r.constraint_name;
  20. dbms_output.put_line(sql_command);
  21. execute immediate sql_command;
  22. END LOOP ;
  23. END ;
  24. /
  25. Statement processed.
  26. alter table "XXX"."C1" disable constraint C1_FK
  27. alter table "XXX"."C2" disable constraint C2_FK
  28. alter table "XXX"."C3" disable constraint C3_FK
  29. alter table "XXX"."C4" disable constraint C4_FK
  30. DECLARE
  31. sql_command varchar2(500);
  32. BEGIN
  33. FOR r IN (
  34. SELECT '"' || a.owner
  35. || '"."'
  36. || a.table_name
  37. || '"' AS full_table_name,
  38. a.constraint_name
  39. FROM user_constraints a
  40. JOIN user_constraints b
  41. ON ( a.r_constraint_name = b.constraint_name
  42. AND a.r_owner = b.owner )
  43. WHERE a.constraint_type = 'R'
  44. AND a.status = 'DISABLED'
  45. ORDER BY 1 )
  46. LOOP
  47. sql_command := 'alter table ' || r.full_table_name || ' enable constraint ' || r.constraint_name;
  48. dbms_output.put_line(sql_command);
  49. execute immediate sql_command;
  50. END LOOP ;
  51. END ;
  52. /
  53. Statement processed.
  54. alter table "XXX"."C1" enable constraint C1_FK
  55. alter table "XXX"."C2" enable constraint C2_FK
  56. alter table "XXX"."C3" enable constraint C3_FK
  57. alter table "XXX"."C4" enable constraint
5jdjgkvh

5jdjgkvh1#

如果你试图从父表中删除行,你不能禁用和重新启用它的外键。通常,子表中仍会有指向父行(现在已丢失)的行。
所以要么你需要

  • 同时从子表中删除/将其FK列设置为null
  • 使用novalidate选项重新启用外键

如果您试图从父级中删除的是“坏数据”,那么您几乎肯定也希望从子级中删除它。在这种情况下,您还需要对这些子表运行删除/更新。
这使得启用/禁用FK的过程是相当浪费的工作。只要在表中从子表到父表运行DML,它就“正常工作”,同时启用FK。
如果你想简化这个过程,你可以将外键声明为on delete [cascade | set null]。这会自动将所有删除从父表传播到子表。

设置on delete cascade时要非常小心。有了这个,在一个小的查找表上运行一个delete就很容易了,最终会清除大量的数据!

例如:

  1. create table par1 ( c1 int constraint p1_pk primary key );
  2. create table par2 ( c2 int constraint p2_pk primary key );
  3. create table chld (
  4. c1
  5. constraint chld_fk_1 references par1
  6. on delete cascade,
  7. c2
  8. constraint chld_fk_2 references par2
  9. on delete set null
  10. );
  11. insert into par1 values ( 1 );
  12. insert into par1 values ( 2 );
  13. insert into par2 values ( 1 );
  14. insert into par2 values ( 2 );
  15. insert into chld values ( 1, 1 );
  16. insert into chld values ( 2, 2 );
  17. select * from chld;
  18. /*
  19. C1 C2
  20. ---------- ----------
  21. 1 1
  22. 2 2
  23. */
  24. commit;
  25. delete par1 where c1 = 1;
  26. delete par2 where c2 = 2;
  27. select * from chld;
  28. /*
  29. C1 C2
  30. ---------- ----------
  31. 2 <null>
  32. */
展开查看全部

相关问题