英文:
Disable/Enable FK for REFERENCES
问题
我正在尝试自动化一个流程，以禁用和启用外键，以便我可以删除“坏数据”。
下面是我们一个应用程序模式的示例。我找到了Tom Kyte的查询，以显示一些表之间的关系。
创建表c（x NUMBER，
CONSTRAINT c_pk PRIMARY KEY(x)
）;
创建表c1（x NUMBER，
CONSTRAINT c1_pk PRIMARY KEY(x),
CONSTRAINT c1_fk FOREIGN KEY (x) REFERENCES c(x)）;
创建表c2（x NUMBER，
CONSTRAINT c2_pk PRIMARY KEY(x),
CONSTRAINT c2_fk FOREIGN KEY (x) REFERENCES c2(x)）;
创建表c3（x NUMBER，
CONSTRAINT c3_pk PRIMARY KEY(x),
CONSTRAINT c3_fk FOREIGN KEY (x) REFERENCES c2(x)）;
创建表c4（x NUMBER，
CONSTRAINT c4_pk PRIMARY KEY(x),
CONSTRAINT c4_fk FOREIGN KEY (x) REFERENCES c2(x)）;
/* 子/父关系 */
with cte as (
select table_name, constraint_type, constraint_name, r_constraint_name,
max(decode(constraint_type,'R',1,0)) over(partition by table_name) is_r
from user_constraints
where constraint_type in ('P', 'U', 'R')
)
, child_parent as (
select distinct s.table_name child, d.table_name parent, d.constraint_type
from (select * from cte where constraint_type = 'R' or is_r = 0) s
left join cte d
on s.r_constraint_name = d.constraint_name
and s.table_name != d.table_name
)
select level lvl, child, parent, constraint_type
from child_parent
start with parent is null
connect by parent = prior child
order siblings by parent, child;
LVL CHILD PARENT CONSTRAINT_TYPE
1 C - -
2 C1 C P
1 C2 - -
2 C3 C2 P
2 C4 C2 P
我有以下代码，它将禁用和启用FK。我知道这可以合并成一个代码片段（过程，接受参数？？？），但我还没有到达那一点。
从下面的输出中可以看到，它禁用和启用模式中的所有FK，而我只想要能针对一组引用执行此操作。
例如，如果我传入表“C”，我的代码应该只禁用和启用FK C1_FK。如果我传入表“C2”，我的代码应该只禁用和启用C3_FK，C4_FK。
任何帮助将不胜感激。感谢所有回答的人。请注意，如果有更好的编码此解决方案的方法，我愿意接受所有建议。
/* 禁用外键 */
DECLARE
sql_command varchar2(500);
BEGIN
FOR r IN (
SELECT '"' || a.owner
|| '"."'
|| a.table_name
|| '"' AS full_table_name,
a.constraint_name
FROM user_constraints a
JOIN user_constraints b
ON ( a.r_constraint_name = b.constraint_name
AND a.r_owner = b.owner )
WHERE a.constraint_type = 'R'
AND a.status = 'ENABLED'
ORDER BY 1 )
LOOP
sql_command := 'alter table ' || r.full_table_name || ' disable constraint ' || r.constraint_name;
dbms_output.put_line(sql_command);
execute immediate sql_command;
END LOOP ;
END ;
/
Statement processed.
alter table "XXX"."C1" disable constraint C1_FK
alter table "XXX"."C2" disable constraint C2_FK
alter table "XXX"."C3" disable constraint C3_FK
alter table "XXX"."C4" disable constraint C4_FK
DECLARE
sql_command varchar2(500);
BEGIN
FOR r IN (
SELECT '"' || a.owner
|| '"."'
|| a.table_name
|| '"' AS full_table_name,
a.constraint_name
FROM user_constraints a
JOIN user_constraints b
ON ( a.r_constraint_name = b.constraint_name
AND a.r_owner = b.owner )
WHERE a.constraint_type = 'R'
AND a.status = 'DISABLED'
ORDER BY 1 )
LOOP
sql_command := 'alter table ' || r.full_table_name || ' enable constraint ' || r.constraint_name;
dbms_output.put_line(sql_command);
execute immediate sql_command;
END LOOP ;
END ;
/
Statement processed.
alter table "XXX"."C1" enable constraint C1_FK
alter table "XXX"."C2" enable constraint C2_FK
alter table "XXX"."C3" enable constraint C3_FK
alter table "XXX"."C4" enable constraint
<details>
<summary>英文:</summary>
I am trying to automate a process to disable and enable foreign keys so I can delete "bad data".
Below is an example of one of our application schemas. I found this Tom Kyte query to show the relationships between some tables.
create table c ( x NUMBER,
CONSTRAINT c_pk PRIMARY KEY(x)
);
create table c1 ( x NUMBER,
CONSTRAINT c1_pk PRIMARY KEY(x),
CONSTRAINT c1_fk FOREIGN KEY (x) REFERENCES c(x));
create table c2 ( x NUMBER,
CONSTRAINT c2_pk PRIMARY KEY(x),
CONSTRAINT c2_fk FOREIGN KEY (x) REFERENCES c2(x));
create table c3 ( x NUMBER,
CONSTRAINT c3_pk PRIMARY KEY(x),
CONSTRAINT c3_fk FOREIGN KEY (x) REFERENCES c2(x));
create table c4 ( x NUMBER,
CONSTRAINT c4_pk PRIMARY KEY(x),
CONSTRAINT c4_fk FOREIGN KEY (x) REFERENCES c2(x));
/* child/parent relationship */
with cte as (
select table_name, constraint_type, constraint_name, r_constraint_name,
max(decode(constraint_type,'R',1,0)) over(partition by table_name) is_r
from user_constraints
where constraint_type in ('P', 'U', 'R')
)
, child_parent as (
select distinct s.table_name child, d.table_name parent, d.constraint_type
from (select * from cte where constraint_type = 'R' or is_r = 0) s
left join cte d
on s.r_constraint_name = d.constraint_name
and s.table_name != d.table_name
)
select level lvl, child, parent, constraint_type
from child_parent
start with parent is null
connect by parent = prior child
order siblings by parent, child;
LVL CHILD PARENT CONSTRAINT_TYPE
1 C - -
2 C1 C P
1 C2 - -
2 C3 C2 P
2 C4 C2 P
I have the following code, which will disable and enable FK's. I know this can be merged into one piece of code(procedure, which accepts parameter???) but I haven't gotten to that point yet.
As you can see from the output below it disables and enables ALL FK'S in the schema where I only want code that will do it for one set of references.
For example, if I pass in table "C" my code should only disable and enable FK C1_FK. If I pass in table "C2" my code should only disable and enable C3_FK, C4_FK
Any help would be greatly appreciated. Thanks in advance to all who answer. Note if there is a better way to code this solution I'm open to all suggestions
/* disable foreign keys */
DECLARE
sql_command varchar2(500);
BEGIN
FOR r IN (
SELECT '"' || a.owner
|| '"."'
|| a.table_name
|| '"' AS full_table_name,
a.constraint_name
FROM user_constraints a
JOIN user_constraints b
ON ( a.r_constraint_name = b.constraint_name
AND a.r_owner = b.owner )
WHERE a.constraint_type = 'R'
AND a.status = 'ENABLED'
ORDER BY 1 )
LOOP
sql_command := 'alter table ' || r.full_table_name || ' disable constraint ' || r.constraint_name;
dbms_output.put_line(sql_command);
execute immediate sql_command;
END LOOP ;
END ;
/
Statement processed.
alter table "XXX"."C1" disable constraint C1_FK
alter table "XXX"."C2" disable constraint C2_FK
alter table "XXX"."C3" disable constraint C3_FK
alter table "XXX"."C4" disable constraint C4_FK
DECLARE
sql_command varchar2(500);
BEGIN
FOR r IN (
SELECT '"' || a.owner
|| '"."'
|| a.table_name
|| '"' AS full_table_name,
a.constraint_name
FROM user_constraints a
JOIN user_constraints b
ON ( a.r_constraint_name = b.constraint_name
AND a.r_owner = b.owner )
WHERE a.constraint_type = 'R'
AND a.status = 'DISABLED'
ORDER BY 1 )
LOOP
sql_command := 'alter table ' || r.full_table_name || ' enable constraint ' || r.constraint_name;
dbms_output.put_line(sql_command);
execute immediate sql_command;
END LOOP ;
END ;
/
Statement processed.
alter table "XXX"."C1" enable constraint C1_FK
alter table "XXX"."C2" enable constraint C2_FK
alter table "XXX"."C3" enable constraint C3_FK
alter table "XXX"."C4" enable constraint
</details>
# 答案1
**得分**: 1
如果你尝试从父表中删除行，你不能禁用并重新启用指向它的外键。通常情况下，仍然会有子表中指向（现在缺失的）父行的行。
因此，你需要：
- 也从子表中删除/将它们的外键列设置为 `null`
- 使用 `novalidate` 选项重新启用外键
如果你试图从父表中删除的是“坏数据”，你几乎肯定也想从子表中删除它。在这种情况下，你需要在这些子表上运行删除/更新。
这使得启用/禁用外键的过程变得相当多余。只需通过从子表到父表运行 DML，它就可以“正常工作”，同时保持启用外键。
如果你想让这个过程更容易，你可以将外键声明为 `on delete [cascade | set null]`。这会自动将父表上的任何删除传播到子表。
**非常小心设置 `on delete cascade`。使用它很容易在小的查找表上运行 `delete`，然后删除大量数据！**
例如：
```sql
create table par1 ( c1 int constraint p1_pk primary key );
create table par2 ( c2 int constraint p2_pk primary key );
create table chld (
c1
constraint chld_fk_1 references par1
on delete cascade,
c2
constraint chld_fk_2 references par2
on delete set null
);
insert into par1 values ( 1 );
insert into par1 values ( 2 );
insert into par2 values ( 1 );
insert into par2 values ( 2 );
insert into chld values ( 1, 1 );
insert into chld values ( 2, 2 );
select * from chld;
/*
C1 C2
---------- ----------
1 1
2 2
*/
commit;
delete par1 where c1 = 1;
delete par2 where c2 = 2;
select * from chld;
/*
C1 C2
---------- ----------
2 <null>
*/
```
<details>
<summary>英文:</summary>
If you're trying to delete rows from a parent table you can't disable & re-enable the foreign keys to it. In general there will still be rows in the child tables pointing to the (now missing) parent row.
So either you need to
* Also delete from the child tables/set their FK columns to `null`
* Reenable the foreign keys with the `novalidate` option
If it's "bad data" you're trying to remove from the parent, you almost certainly want to remove it from the children as well. In which case you need to run deletes/updates on these child tables too.
Which makes the process of enabling/disabling the FKs a rather wasted effort. Just run the DML through the tables from the children up to the parents and it "just works" while leaving the FKs enabled.
If you want to make this process easier, you can declare the foreign keys as `on delete [cascade | set null]`. This automatically propagates any deletes from the parent table to the children.
**Be very careful setting `on delete cascade`. With this it's very easy to run a `delete` on a small lookup table and end up wiping out huge amounts of data!**
For example:
create table par1 ( c1 int constraint p1_pk primary key );
create table par2 ( c2 int constraint p2_pk primary key );
create table chld (
c1
constraint chld_fk_1 references par1
on delete cascade,
c2
constraint chld_fk_2 references par2
on delete set null
);
insert into par1 values ( 1 );
insert into par1 values ( 2 );
insert into par2 values ( 1 );
insert into par2 values ( 2 );
insert into chld values ( 1, 1 );
insert into chld values ( 2, 2 );
select * from chld;
/*
C1 C2
---------- ----------
1 1
2 2
*/
commit;
delete par1 where c1 = 1;
delete par2 where c2 = 2;
select * from chld;
/*
C1 C2
---------- ----------
2 <null>
*/
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论