[EMAIL PROTECTED] wrote:
Hi, I am trying to use the foreign key constraints from InnoDB
and creating indexes is a requirement for foreign key.
The problem is that by creating index for my foreign key,
it does not allow my foreign key to have null or blank values which my records will have.
For eg. a BorrowerID is a foreign key on a Book table, but when the book is not borrowed, the BorrowerID will be null and I can't seem to import the data containing null values for the foreign key.
Is there a way to solve this? Thanks.
If you have a foreign key constraint that requires the foreign key field to be populated then you effectively have a "MANY TO ONE" with Min of 1 and Max of 1 relationship between the book table and the borrower table -- thus you cannot have a book without a borrower. That is a logical problem, and the one you are describing.
Separate the BorrowerID from table `book`. Make a table called, oh, `book_borrower` and put
BookID BorrowerID DateOut DateDue DateReturned ...
Then you can query for borrowed books using a join like:
SELECT a.BookID, b.BorrowerID, c.FullName from ( book a inner join book_borrower b on a.BookID = b.BookID ) inner join borrower c on b.BorrowerID = c.BorrowerID WHERE b.DateReturned is null
Available books could be found:
SELECT a.BookID from book a left join book_borrower b on a.BookID=b.BookID WHERE b.BookID is null and b.DateReturned is null
(Which says show me all the book.BookID that fail to have a non-returned book in the book_borrower table. Usually I put the "and b.DateReturned is null" with the join statement, instead of the WHERE clause.)
Now you can have a book without a borrower and can easily track borrowing history for books and borrowers. Be sure to index the fields you'll be using as selection criteria!
HTH,
Robert J Taylor [EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
