重命名或禁用oracle外键来删除父表

Question

我有一个表中有一个用作多个表中的FK的PK。由于我遇到的问题，我不得不放弃父表，但由于FK，它不会让我。我尝试从父表中删除PK和UK，并且错误地指出仍有使用它们的表。我甚至在多张桌子上禁用了FK，并尝试删除表格，PK和UK，并且仍然表示有与他们绑定的表格。

有什么办法可以在oracle中删除父表，然后用完全相同的PK和UK重新创建它，而不必从多个表中删除FK？

谢谢

Answer 1

有什么方法可以让我在oracle中删除一个父表，然后用完全相同的PK和英国重新 它，而无需从 多个表中删除的FK？

是的，您使用DBMS_REDEFINITION。您可以使用DBMS_REDEFINITION做任何你需要做的，因为这“修复”的一部分：重建表，添加/删除列，重新排序列，添加分区等

你唯一不能真的做的是改变主键的结构。但是，如果你这样做了，无论如何你都会放弃所有的FK。

这里有一个简单的例子：

-- Create a parent/child relationship with data.... 
CREATE TABLE matt_parent 
(
    id  NUMBER, 
    value1 VARCHAR2 (30), 
    CONSTRAINT matt_parent_pk PRIMARY KEY (id) 
); 

CREATE TABLE matt_child 
(
    id   NUMBER, 
    parent_id NUMBER, 
    value2  VARCHAR2 (30), 
    CONSTRAINT matt_child_pk PRIMARY KEY (id), 
    CONSTRAINT matt_child_fk1 FOREIGN KEY (parent_id) REFERENCES matt_parent (id) 
); 

insert into matt_parent values (1, 'XXX'); 

insert into matt_child values (100, 1, 'YYY'); 

COMMIT; 

-- These next few SQLs use online redefinition to change the parent table (adding a column and adding partitioning) 
-- First, have Oracle check whether redefinition is going to work 
BEGIN 
DBMS_REDEFINITION.CAN_REDEF_TABLE('apps','matt_parent', 
     DBMS_REDEFINITION.CONS_USE_ROWID); 
END; 
/

-- Second, create our rebuilt table. It eventually replaces our current table. 
CREATE TABLE apps.matt_parent_redef 
    ( 
    id  NUMBER, 
    date1 DATE, 
    value1 VARCHAR2 (30) 
) 
PARTITION BY RANGE (date1) 
INTERVAL (NUMTODSINTERVAL (1,'DAY')) (partition parent_old values less than (to_date('01-JAN-2000','DD-MON-YYYY'))); 
; 

-- Run this if you need to start over... 
--BEGIN 
--DBMS_REDEFINITION.ABORT_REDEF_TABLE (
-- uname => 'apps', 
-- orig_table => 'matt_parent', 
-- int_table => 'matt_parent_redef'); 
--END; 


-- Third, tell Oracle to start the redefinition process. Can take long -- copies all the data into our new table 
BEGIN 
DBMS_REDEFINITION.START_REDEF_TABLE('apps', 'matt_parent','matt_parent_redef', 
     'id, sysdate date1, value1', 
     dbms_redefinition.cons_use_rowid); 
END; 
/

-- Fourth, tell Oracle to copy dependent objects like PK constaints, FKs, grants, etc 
DECLARE 
num_errors PLS_INTEGER; 
BEGIN 
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('apps', 'matt_parent','matt_parent_redef', 
    DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors); 

    DBMS_OUTPUT.PUT_LINE('Copy depenedent objects: num_errors = ' || num_errors); 
END; 


-- Fifth, query to make sure there were no problems. This should return no rows 
select * from   DBA_REDEFINITION_ERRORS where base_table_name = 'MATT_PARENT'; 


-- Sixth, tell Oracle we're finished. This *briefly* locks the table as the switch is accomplished. 
BEGIN 
DBMS_REDEFINITION.FINISH_REDEF_TABLE('apps', 'matt_parent', 'matt_parent_redef'); 
END; 
/

-- Later, drop the redefinition version of the table. 
DROP TABLE apps.matt_parent_redef CASCADE CONSTRAINTS; 

-- Try to delete some parent data, just to verify our FKs are still in force. 
delete from matt_parent where id = 1;