Composite Foreign key in ORACLE

0
votes

I an trying to convert database schema into Database in ORACLE SQL

Books (BookID,PubID,Title,Author,Price,Availability)

Publisher (PubID,Name,Address,BookID)

Member (MemberID,Name,Address,MemberType,MemberDate,ExpiryDate)

Barrowings (MemberID,BookID,IssuedDate,ReturnDate,DueDate)

I have done first 3 tables. But i have a problem with the 4th table (Borrowings) (MemberID,BookID,IssueDate) I want to make first 2 columns (MemberID,BookID) as a composite foreign key (Should refere 2 different tables)

MemberID referes the MemberID colimn in Member table BookID referes the BookID table in Books table. IssiedDate column is the primary key of that table How to make a composite foreign key in ORACLE SQL

2
sqloracleforeign-keys
Please show your create statements for the tables so far. FYI, no pl/sql needed for any of this. - OldProgrammer
"IssuedDate is the primary key of that table" - means that you can have exactly only one borrowing per day. You can't have two people borrow two different books on the same day then. - a_horse_with_no_name

2 Answers

1
votes

You cannot create one foreign key referring to 2 parent tables.

In this case you need to create 2 foreign key constraints, one to BOOKS and other to MEMBER.

0
votes

Create table borrowings( MemberID varchar2(20) constraint fk_memid reference member(MemberID), BookID varchar2(20) constraint fk_bookid reference Books(BookID) )

For this purpose first you have to give primary key or Unique on patent tables columns. I.e BookID column from books and memberid column from members..