Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have created two tables

create table ref1(id varchar2(3) primary key);
create table ref2(id varchar2(3) primary key);

Now I want to create a third table with a single column as foreign key for both ref1.id and ref2.id

create table actual(p varchar2(3) );

How can i do it?

share|improve this question

migrated from stackoverflow.com Oct 15 '12 at 6:32

This question came from our site for professional and enthusiast programmers.

2  
That sounds very strange. To me this implies a one-to-relationship between ref1 and ref2 so I don't see the benefit in this. What is it that you are really trying to achieve. Explain your real problem. –  a_horse_with_no_name Oct 11 '12 at 12:03
2  
@a_horse: Suppose we have Manager and Consultant tables (both with 1:1 relationship with Person). A person can be manager or consultant or both or neither. Then we have a table Decision with a column Authorized_by_Person_ID which we want to ensure they (the decisions) are made by someone that is both a manager and a consultant. –  ypercube Oct 11 '12 at 12:31
    
@ypercube: thanks, nice example. I never thought about modelling something like that this way. –  a_horse_with_no_name Oct 11 '12 at 12:34
    
@ypercube But if a single column in Person references both of the other tables, then either it must be NULL (i.e. the person is neither manager nor consultant) or it must have a single value that matches rows in both of the other tables (i.e. the person is both manager and consultant). If you wanted the ability to be one or the other but not both, wouldn't you need two separate columns in Person, one for ManagerID and one for ConsultantID? –  Dave Costa Oct 11 '12 at 12:40
    
@DaveCosta: No, I meant the FKs to be the other way around. From Manager to Person and from Consultant to Person. No Nulls and no FKs from Person to the other 2 tables. –  ypercube Oct 11 '12 at 12:45

1 Answer 1

You could try this:

CREATE TABLE ref1
(
    id VARCHAR2(3) PRIMARY KEY
);

CREATE TABLE ref2
(
    id VARCHAR2(3) PRIMARY KEY
);

CREATE TABLE look
(
    p VARCHAR2(3),
    CONSTRAINT fk_p_ref1 FOREIGN KEY (p) REFERENCES ref1(id),
    CONSTRAINT fk_p_ref2 FOREIGN KEY (p) REFERENCES ref2(id)
);

Tested on a 10G R2 server with no complaint (yet).


Edit:

Simple test result:

SQL> insert into ref1 (id) values ('abc');

1 row created.

SQL> insert into ref2 (id) values ('def');

1 row created.

SQL> insert into look (p) values ('abc');
insert into look (p) values ('abc')
*
ERROR at line 1:
ORA-02291: integrity constraint (LINEQZ.FK_P_REF2) violated - parent key not
found


SQL> insert into ref2 (id) values ('abc');

1 row created.

SQL> insert into look (p) values ('abc');

1 row created.
share|improve this answer
3  
Impressive. One never stops learning. –  a_horse_with_no_name Oct 11 '12 at 12:01
2  
It even works when the FKs are defined inline: CREATE TABLE look( p VARCHAR(3) REFERENCES ref1(id) REFERENCES ref2(id) ); –  ypercube Oct 12 '12 at 18:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.