| foreign key [message #78821] |
Tue, 19 March 2002 01:05  |
Angela
Messages: 13
Registered: October 2000
|
Junior Member |
|
|
What I'm trying to do is: I want the "information_name" (set as foreign key) can be whether it is from "book_name" from table book, or "magazine_name" from table magazine. So, reader can choose book or magazine (only can choose one of them). I've created "book_name" as primary key in table book, and "magazine_name" as primary key in table magazine.
I've already inserted data on table book and magazine, and it has already matching with the table reader.
So, if my mistake lies on the design of the table, how should it be?
|
|
|
|
| Re: foreign key [message #78824 is a reply to message #78821] |
Tue, 19 March 2002 01:29  |
pratap kumar tripathy
Messages: 660
Registered: January 2002
|
Senior Member |
|
|
u have two option
1.
READER_ID NOT NULL VARCHAR2(10)
READER_NAME NOT NULL VARCHAR2(30)
INFORMATION_NAME1 VARCHAR2(30)
INFORMATION_NAME2 VARCHAR2(30)
EDITION_NO NOT NULL VARCHAR2(4)
where INFORMATION_NAME1 will be foreign_key for book_name
and INFORMATION_NAME2 will be foreign_key for magazine_name
2.
READER_ID NOT NULL VARCHAR2(10)
READER_NAME NOT NULL VARCHAR2(30)
book_or_magazine VARCHAR2(1)
INFORMATION_NAME VARCHAR2(30)
EDITION_NO NOT NULL VARCHAR2(4)
where book_or_magazine is a switch between book and magazine it may be of "B" or "M", if it is B then information_name will store book_name
and if it is M then information_name will store magazine_name.
but in this case u can not enforce foreign key , u have to write trigger for enforcing constraints
3.u can put the code book_or_magazine in book table and instead of two table book and magazine u can have one table.
hope it helps!!!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
