2 foreign keys reference [message #403719] |
Mon, 18 May 2009 05:23  |
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 #403729 is a reply to message #403719] |
Mon, 18 May 2009 06:19   |
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
|
|
|
|