| One column has 3 foreign key pointing to other 3 tables [message #60480] |
Tue, 10 February 2004 07:31  |
Jadie
Messages: 64
Registered: January 2002
|
Member |
|
|
I am getting a problem with creating foreign keys on one coclumn which will piont to 3 defferent takbe, for example:
tableA (id number primary key, ref_id number);
tableA1 (id number primary key);
tableA2 (id number primary key);
tableA3 (id number primary key);
Column ref_id will have foreign keys references to TableA1(id), TableA2(id) and TableA3(id). How can I do that? Appreciate any answers!! Thanks
Jadie
|
|
|
|
| Re: One column has 3 foreign key pointing to other 3 tables [message #60485 is a reply to message #60480] |
Tue, 10 February 2004 11:01  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
and the problem is ___ ?
Are you sure you want to have such a relationship ?
If yes,
SQL> create table tableA(id number primary key,ref_id number);
Table created.
SQL> create table tableA1(id number primary key);
Table created.
SQL> create table tableA2(id number primary key);
Table created.
SQL> create table tableA3(id number primary key);
Table created.
SQL> alter table tableA add constraint tableA1_FK foreign key(ref_id) references tableA1(id);
Table altered.
SQL> alter table tableA add constraint tableA2_FK foreign key(ref_id) references tableA2(id);
Table altered.
SQL> alter table tableA add constraint tableA3_FK foreign key(ref_id) references tableA3(id);
Table altered.
SQL> insert into tableA1 values(1);
1 row created.
SQL> insert into tableA2 values(1);
1 row created.
SQL> insert into tableA3 values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into tableA values(1,1);
insert into tableA values(1,1)
*
ERROR at line 1:
ORA-02291: integrity constraint (THIRU.TABLEA3_FK) violated - parent key not
found
SQL> insert into tableA3 values(1);
1 row created.
SQL> insert into tableA values(1,1);
1 row created.
SQL> commit;
Commit complete
|
|
|
|
| Re: One column has 3 foreign key pointing to other 3 tables [message #60486 is a reply to message #60485] |
Tue, 10 February 2004 11:56 |
Jadie
Messages: 64
Registered: January 2002
|
Member |
|
|
Yes, I need this relastionship... Applications require this data structure. And these are the real statements I tried to use to create the foreign keys:
ALTER TABLE SETTINGS
ADD CONSTRAINT settings_FK02 (ref_id)
REFERENCES readers(id);
ALTER TABLE SETTINGS
ADD CONSTRAINT settings_FK03 (ref_id)
REFERENCES merchants(id);
ALTER TABLE SETTINGS
ADD CONSTRAINT settings_FK04 (ref_id)
REFERENCES terminals(id);
I get the error:
cannot validate (TIMS01.SETTINGS_FK01) - parent keys not found
....
I don't know why! HELP :)
Jadie
|
|
|
|
| Re: One column has 3 foreign key pointing to other 3 tables [message #60488 is a reply to message #60486] |
Tue, 10 February 2004 12:42 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
this is becos you have some keys on the child table that does not exist in the parent table and you are trying to create a foreign key constraint. You either need to remove from the child or add them to the parent and then create the FK constraint.
-- this is the parent
SQL> create table pt(x int);
Table created.
SQL> insert into pt values(1);
1 row created.
SQL> commit;
Commit complete.
-- this is the child
SQL> create table cd(x int);
Table created.
-- Lets insert 2 , which doesnt exist in the parent
SQL> insert into cd values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> alter table pt add primary key(x);
Table altered.
-- try adding a FK
SQL> alter table cd add constraint cd_fk foreign key(x) references pt;
alter table cd add constraint cd_fk foreign key(x) references pt
*
ERROR at line 1:
ORA-02298: cannot validate (THIRU.CD_FK) - parent keys not found
-- fix the parent
SQL> insert into pt values(2);
1 row created.
-- retry adding the FK
SQL> alter table cd add constraint cd_fk foreign key(x) references pt;
Table altered.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
