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);