kyuubi@stripped 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
robert@stripped
