| foregin composite key [message #18876] |
Tue, 19 February 2002 18:54  |
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  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
