Home » SQL & PL/SQL » SQL & PL/SQL » Foreign key constraint error (Oracle 9i)
Foreign key constraint error [message #428153] Tue, 27 October 2009 06:42 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I am not able to truncate a table even when the table in relation has no data.

I have inserted data in first table as :-
INSERT INTO smp_good_return_note
          SELECT grn_number, line_location_id, vendor_lot_no, shalina_lot_no,
                 manufacturer_date, expiry_date, supplied_quantity,
                 rejected_quantity, vendor_code, rejected_by, rejected_on,
                 rejected_remarks, grndate, challan_number, inv_ref_number
            FROM parking_shalina_erp_data p, SMP_PO_SCHEDULE s
           WHERE grn_number IS NOT NULL AND line_location_id IS NOT NULL
           and p.LINE_LOCATION_ID = s.LINELOCATIONID;



And in second table as :-

INSERT INTO smp_po_schedule
          SELECT to_number(line_location_id), po_number, scheduledquantity, po_need_by_date
            FROM parking_shalina_erp_data p,  smp_vendor_location_item v
           WHERE p.line_location_id IS NOT NULL
             AND p.po_number IS NOT NULL
             AND p.po_need_by_date IS NOT NULL
             AND v.vendor_id = TO_NUMBER (p.vendor_code) 
             AND v.itemcode = p.item_code
           group by to_number(line_location_id),po_number,
            scheduledquantity, po_need_by_date;  



I have a foreign key on table first table smp_good_return_note as :-

ALTER TABLE SMP_GOOD_RETURN_NOTE ADD ( CONSTRAINT FK_LINELOCATIONID FOREIGN KEY (LINELOCATIONID) 
 REFERENCES SMP_PO_SCHEDULE (LINELOCATIONID)


I have to truncate the two tables.

1). TRUNCATE TABLE smp_good_return_note -->> I am able to do this.
TRUNCATE TABLE smp_po_schedule. -->> Not able to do this...gives error
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


If I drop the constraint on table smp_po_schedule :-
ALTER TABLE SMP_GOOD_RETURN_NOTE DROP constraint FK_LINELOCATIONID


Then I am able to trucnate the table smp_po_schedule.

Why I have to drop the constraint everytime when I want to truncate the table.....?

Is there a way that I can simply truncate the tables in their order ?

Please suggest me as where I am going wrong...

Mona
Re: Foreign key constraint error [message #428154 is a reply to message #428153] Tue, 27 October 2009 06:44 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can just disable the foreign keys instead of drop them.
Re: Foreign key constraint error [message #428159 is a reply to message #428154] Tue, 27 October 2009 06:48 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

But do I really need to disable them when there is no data in table smp_good_return_note.
My work will increase I will have to enable them again.

Is there no other way?

[Updated on: Tue, 27 October 2009 06:49]

Report message to a moderator

Re: Foreign key constraint error [message #428160 is a reply to message #428153] Tue, 27 October 2009 06:49 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Yes - oracle doesn't bother to check if there's any data it just checks if there are enabled foreign keys
Re: Foreign key constraint error [message #428162 is a reply to message #428160] Tue, 27 October 2009 06:50 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks I used :-

EXECUTE IMMEDIATE 'ALTER TABLE SMP_GOOD_RETURN_NOTE DISABLE constraint FK_LINELOCATIONID';


And it worked.

[Updated on: Tue, 27 October 2009 06:55]

Report message to a moderator

Re: Foreign key constraint error [message #428166 is a reply to message #428162] Tue, 27 October 2009 06:54 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
use keyword "DISABLE" instead of "DROP"
Re: Foreign key constraint error [message #428167 is a reply to message #428153] Tue, 27 October 2009 06:56 Go to previous message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
<never mind>
EDIT: post rendered redundant after OP changed last post.

[Updated on: Tue, 27 October 2009 06:57]

Report message to a moderator

Previous Topic: how to create boolean data type in oracle??
Next Topic: From Access2002 to Oracle
Goto Forum:
  


Current Time: Sun Sep 25 00:55:56 CDT 2016

Total time taken to generate the page: 0.11319 seconds