| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Foreign Key Deletion problem
Environment: Windows XP SP2
Database: Oracle 9.2.0.6
I have a table with self-referencing Foreign Keys; here is the
definition:
CREATE TABLE fin_invoice
(
invoice_nbr number not
null,
location_id varchar2(10) not
null,
customer_vendor_id varchar2(10) not
null,
financial_source char(3) not
null
CONSTRAINT ckc_fin_inv_financial_source CHECK (financial_source
IN ('A/P','A/R')),
journal_source_code varchar2(10) not
null,
invoice_id varchar2(10) not
null,
term_code varchar2(10) null,
description varchar2(50) null,
currency_code varchar2(10) not
null,
invoice_date date not
null,
journal_nbr number(10) null,
check_list_id varchar2(10) null,
invoice_status char(1) not
null
CONSTRAINT ckc_fin_invoice_status CHECK (invoice_status IN
('O','C')),
discount_date date null,
due_date date not
null,
bank_id varchar2(10) null,
balance_amount number(18,6) not
null,
original_amount number(18,6) not
null,
transaction_nbr number null,
reversed_transaction_nbr number DEFAULT 0 not
null,
gl_exchange_rate number(10,6) null,
plc_exchange_rate number(10,6) null,
cust_vend_exchange_rate number(10,6) null,
print_flag char(1) DEFAULT 'N' not
null
CONSTRAINT ckc_fin_invoice_print_flag CHECK (print_flag IN
('Y','N')),
contra_amount number(18,6) null,
balance_forward_print_flag char(1) DEFAULT 'N' not
null
CONSTRAINT ckc_fin_inv_bal_for_print_flag CHECK
(balance_forward_print_flag IN ('Y','N')),
reference_id varchar2(10) null,
discount_percent number(10,6) null,
discount_amount number(18,6) null,
quick_check_nbr number null,
quick_check_date date null,
gl_date date null,
reversed_journal_nbr number null,
discount_taken_amount number(18,6) DEFAULT 0 not
null,
check_list_discount number(18,6) null,
interest_calculation_date date null,
interest_grace_days number null,
interest_grace_amount number(18,6) null,
interest_percent number(5,3) null,
interest_min_amount number(18,6) null,
interest_ioi_flag char(1) DEFAULT 'N' not
null
CONSTRAINT ckc_fin_inv_interest_ioi_flag CHECK
(interest_ioi_flag IN ('Y','N')),
control_acct_nbr varchar2(22) not
null,
row_source varchar(2) null
CONSTRAINT ckc_fin_inv_row_source CHECK
(row_source is null OR (row_source IN
('A','S','VA','VS','DC','AC','U','I','OP','RC'))),
row_source_nbr number null,
quick_check_amount number(18,6) null,
ngc_id varchar2(10) null,
add_by varchar2(40) DEFAULT user not
null,
add_date date DEFAULT sysdate not
null,
change_by varchar2(40) DEFAULT user not
null,
change_date date DEFAULT sysdate not
null,
check_list_pay_amount number(18,6) null,
last_transaction_date date DEFAULT sysdate not
null,
qc_journal_nbr number null,
payment_method_code varchar2(10) null,
hold_payment_flag char(1) DEFAULT 'N' not
null
CONSTRAINT ckc_fin_inv_hold_payment_flag CHECK
(hold_payment_flag IN ('Y','N')),
trader_id varchar2(10) null,
tax_1_code varchar2(10) null,
tax_1_nbr number(5) null,
tax_2_flag char(1) DEFAULT 'N' not
null
CONSTRAINT ckc_fin_inv_tax_2_flag CHECK (tax_2_flag IN
('Y','N')),
tax_3_flag char(1) DEFAULT 'N' not
null
CONSTRAINT ckc_fin_inv_tax_3_flag CHECK (tax_3_flag IN
('Y','N')),
dp_interest_flag char(1) DEFAULT 'N' not
null
CONSTRAINT ckc_fin_inv_dp_interest_flag CHECK (dp_interest_flag
IN ('Y','N')),
payment_eligible_flag char(1) DEFAULT 'Y' not
null
CONSTRAINT ckc_fininv_paymenteligibleflag CHECK
(payment_eligible_flag IN ('Y','N')),
interest_invoice_nbr number null,
CONSTRAINT pk_fin_invoice PRIMARY KEY (invoice_nbr)
using index
tablespace smartsoft_index
I also have a Foreign Key defined as follows: ALTER TABLE fin_invoice
ADD CONSTRAINT fk7_fin_invoice FOREIGN KEY (interest_invoice_nbr)
REFERENCES fin_invoice (invoice_nbr)
/
in addition to several other Foreign Keys, which are NOT
self-referencing.
Notice that this Foreign Key is self-referencing; the business case is that an invoice may also generate a separate invoice for interest calculations only.
I would like to TRUNCATE (not DELETE) the data in this table; I do NOT want to log any of this activity. This code will be executed within an application via a PURGE function; it will not be executed externally via SQL or SQL*PLUS.
Is it possible to DISABLE all Foreign Keys on this table; preferrably, I would like to DISABLE all Foreign Keys on this table, perform the TRUNCATE operation, and then ENABLE the Foreign Keys again.
I have tried the following SQL:
alter table fin_invoice
disable CONSTRAINT fk7_fin_invoice
;
which works correctly, but I am looking for a more generic solution:
the name of the Foreign Key (i.e. fk7_fin_invoice) may change due to
design changes.
I have also tried the following SQL:
alter table fin_invoice
disable CONSTRAINT all
;
but it produces this error:
ERROR at line 2:
ORA-02250: missing or invalid constraint name
I was hoping to use SQL similar to this for a trigger:
alter trigger <name of the trigger> disable/enable
;
but have not found anything yet.
Thanks
Murray Sobol
dbcSMARTsoftware inc.
Received on Thu Oct 13 2005 - 10:43:35 CDT
 |
 |