how to use dual foreign key in oracle 10g

Question

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?

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.