解决oracle表的主键被另一个表作为外键关联使用后主表无法清空问题思路

select c.constraint_name,
       c.table_name,
       c.constraint_type,
       c.r_constraint_name
  from user_constraints c
 where c.r_constraint_name like ='%DMS_BD_VORDERMAS%'

   and c.constraint_type = 'R'

 

1主表定义

-- Create table
create table DMS_BD_VORDERMAS
(
  ID              VARCHAR2(30),
  CUSTORDERCODE   VARCHAR2(30) not null,
  ORDERDATE       DATE,
  PATHCODE        VARCHAR2(30),
  CUSTCODE        VARCHAR2(30),
  DELIVERYMANCODE VARCHAR2(30),
  QUANTITYSUM     NUMBER,
  AMOUNTSUM       NUMBER(9,2)
)
tablespace HUIKE
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 2
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table DMS_BD_VORDERMAS
  add constraint PK_DMS_BD_VORDERMAS primary key (CUSTORDERCODE)
  using index 
  tablespace HUIKE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 576K
    minextents 1
    maxextents unlimited
  );

 

2从表定义（引用以上表的主键作为外键，导致主表不能直接清空，需将此表外键先disable再清空主表，再使从表的外键可用即可

-- Create table
create table DMS_DM_VORDERPRO
(
  ID            VARCHAR2(30),
  PROCODE       VARCHAR2(30) not null,
  CUSTORDERCODE VARCHAR2(30) not null,
  PRONAME       VARCHAR2(30),
  QTY           NUMBER,
  ORDERDATE     DATE,
  REMARK        VARCHAR2(100)
)
tablespace HUIKE
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table DMS_DM_VORDERPRO
  add constraint PK_DMS_DM_VORDERPRO primary key (PROCODE, CUSTORDERCODE)
  using index 
  tablespace HUIKE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table DMS_DM_VORDERPRO
  add constraint FK_DMS_DM_V_REFERENCE_DMS_BD_V foreign key (CUSTORDERCODE)
  references DMS_BD_VORDERMAS (CUSTORDERCODE);