Foreign key constraint error [message #428153] |
Tue, 27 October 2009 06:42  |
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
|
|
|
|
|
|
|
|
|