Home » SQL & PL/SQL » SQL & PL/SQL » foregin composite key
foregin composite key [message #18876] Tue, 19 February 2002 18:54 Go to next message
Tariq Zia lakho
Messages: 8
Registered: February 2002
Junior Member
Hye.
I want to create a foeign compsite key, where i have been creeated Composite primary key on three columns.
column name are as under
1.inv_no
2.reg_no
2.season

when i make composite key with this command an error generated by oracle.
plz give me advice or session
i will be very thankful to u.

ALTER TABLE INV_DTL
ADD CONSTRAINTS INV_NO_REF_NO_SEASON_FK FOREIGN KEY INV_NO,REF_NO,SEASON)
REFERENCES INV_MST

Error return from oracle.
ERROR at line 2:
ORA-02298: cannot validate (UREA.INV_NO_REF_NO_SEASON_FK) - parent keys not found
Re: foregin composite key [message #18890 is a reply to message #18876] Wed, 20 February 2002 03:53 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
you have to cleanup data in inv_dtl table.

follow these steps:

1) create table like below:

create table inv_dtl_exceptions(crowid rowid,
owner varchar2(30),table_name varchar2(30),cons varchar2(30));

2)
ALTER TABLE INV_DTL
ADD CONSTRAINTS INV_NO_REF_NO_SEASON_FK FOREIGN KEY INV_NO,REF_NO,SEASON)
REFERENCES INV_MST
exceptions into inv_dtl_exceptions;

3) join inv_dtl and inv_dtl_exceptions table to get invalid data

select a.* from inv_dtl a,inv_dtl_exceptions b
where a.rowid=b.crowid;

above query returns rows causing error.
Previous Topic: performance
Next Topic: timed_statistics
Goto Forum:
  


Current Time: Mon Sep 27 17:13:26 CDT 2021