Foreign Key Deletion problem

Giganews Newsgroups
Subject: Foreign Key Deletion problem
Posted by:  Murray Sobol (murray_sob…@dbcsmartsoftware.com)
Date: Thu, 13 Oct 2005

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

Replies