Foreign Key Deletion problem
Murray Sobol
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
)
tablespace smartsoft_data
/
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.
jks...@gmail.com
> 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.
Try generating the SQL from all/dba/user_constraints
>
> I have also tried the following SQL:
> alter table fin_invoice
> disable CONSTRAINT all
> ;
Think about it - do you really want to disable *all* constraints?
> 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.
Try disable all triggers:
http://oraclesvca2.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_32a.htm#2075682
Murray Sobol
>Murray Sobol wrote:
>
>> 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.
>
>Try generating the SQL from all/dba/user_constraints
>
>>
>> I have also tried the following SQL:
>> alter table fin_invoice
>> disable CONSTRAINT all
>> ;
>
>Think about it - do you really want to disable *all* constraints?
Yes, but only for the duration of the TRUNCATE operation.
Immediately after that, I would issue a SQL such as:
alter table fin_invoice
enable CONSTRAINT all
if that syntax is available.
>
>> 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.
>
>Try disable all triggers:
>http://oraclesvca2.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_32a.htm#2075682
I dont see what value disabling all Triggers would have when
dealing with Foreign Key issues. The columns involved in the
self-referencing join are popluated by the application, NOT by a
trigger.
Murray
jks...@gmail.com
Murray Sobol wrote:
> On 13 Oct 2005 10:55:10 -0700, jks...@gmail.com wrote:
>
> >
> >
> >Think about it - do you really want to disable *all* constraints?
>
> Yes, but only for the duration of the TRUNCATE operation.
> Immediately after that, I would issue a SQL such as:
> alter table fin_invoice
> enable CONSTRAINT all
> if that syntax is available.
>
Ok, I will speak plainly.
Disabling all constraints would include primary key constraints,
foreign key constraints and check constraints.
Disabling all constraints would not be a good thing.
> >
> >> 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.
> >
> >Try disable all triggers:
>
Perhaps your question should have been phrased differently.
e.g. I was hoping for to use SQL similar to that which is used to
disable all triggers.
Something like that.
At least, it didn't make much sense to me. :)