Home » SQL & PL/SQL » SQL & PL/SQL » 2 foreign keys reference
2 foreign keys reference [message #403719] Mon, 18 May 2009 05:23 Go to next message
LittleB
Messages: 8
Registered: April 2009
Junior Member
Hello all!

I have a query regarding foreign keys. Can a foreign key attribute reference 2 foreign keys from different tables.

I would understand if this is wrong, and I would agree my design could be wrong but please tell me if this is possible.

I have the following tables:

- Customer((PK)Customer_ID, ...)

- Member((PK) Member_ID, (FK)(U)Customer_ID, ...)

- Order ((PK) Order_ID, Customer_Member_ID, ...)

Members are customers who get discount on their orders. Can Customer_Member_ID attribute in Order table reference either Member_ID from member table or Customer_ID from customer table so that discount is applied. I Just need to know if it's possible, any suggestions are welcomed.

Many thanks in advance!!!!


Re: 2 foreign keys reference [message #403726 is a reply to message #403719] Mon, 18 May 2009 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can a foreign key attribute reference 2 foreign keys from different tables.

A foreign key reference a primary or unique constraint not a foreign key.

A member is a customer so you order table should reference customer table and not member one.
In addition, it is most likely that member_id should be an attribute of customer row and the opposite is useless.

Regards
Michel

Re: 2 foreign keys reference [message #403729 is a reply to message #403719] Mon, 18 May 2009 06:19 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@LittleB,

You can create a foreign key on a column which references to two different tables. But it won't behave in the scenario in which you have explained above.

Consider the following example:

SQL> CREATE TABLE customer
  2  (cust_id NUMBER,
  3  CONSTRAINT pk_1 PRIMARY KEY (cust_id));

Table created.

SQL> INSERT INTO customer
  2              (cust_id)
  3     SELECT     LEVEL * 10
  4           FROM DUAL
  5     CONNECT BY LEVEL <= 3;

3 rows created.

SQL> CREATE TABLE member_tab
  2  (mem_id NUMBER,
  3  CONSTRAINT pk_2 PRIMARY KEY (mem_id));

Table created.

SQL> INSERT INTO member_tab
  2              (mem_id)
  3     SELECT     LEVEL * 10
  4           FROM DUAL
  5          WHERE LEVEL NOT IN (1, 2)
  6     CONNECT BY LEVEL <= 5;

3 rows created.

SQL> CREATE TABLE  order_tab
  2  (cl_id NUMBER,
  3  CONSTRAINT fk_1
  4  FOREIGN KEY(cl_id) REFERENCES customer(cust_id),
  5  CONSTRAINT fk_2
  6  FOREIGN KEY(cl_id) REFERENCES member_tab(mem_id)
  7  );

Table created.

SQL> SELECT * FROM customer;

   CUST_ID
----------
        10
        20
        30

SQL> SELECT * FROM member_tab;

    MEM_ID
----------
        30
        40
        50

SQL> INSERT INTO order_tab (cl_id) VALUES (30);
 -- Value Present in both customer & member_tab tables.

1 row created.

SQL>  INSERT INTO order_tab (cl_id) VALUES (10); -- Value not in member_tab table.
 INSERT INTO order_tab (cl_id) VALUES (10)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_2) violated - parent key not found


SQL> INSERT INTO order_tab (cl_id) VALUES (40); -- Value Not In customer table.
INSERT INTO order_tab (cl_id) VALUES (40)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_1) violated - parent key not found


SQL> 



As you can see the foreign key will enforce the integrity based on the data that is present in both the tables. i.e. You will be forced to enter data in ORDER_TAB table i.e. present both in CUSTOMER and MEMBER_TAB tables.

Hope this helps,

[Edit: typo]
Regards,
Jo

[Updated on: Mon, 18 May 2009 06:21]

Report message to a moderator

Re: 2 foreign keys reference [message #403734 is a reply to message #403729] Mon, 18 May 2009 06:49 Go to previous message
LittleB
Messages: 8
Registered: April 2009
Junior Member
Thank you very much to joicejohn and michel, your answers have been very helpful.

Thanks!!!
Previous Topic: How to make a view with rows as columns
Next Topic: Discoverer Query
Goto Forum:
  


Current Time: Sat Dec 10 05:17:26 CST 2016

Total time taken to generate the page: 0.10600 seconds