List:General Discussion« Previous MessageNext Message »
From:Robert J Taylor Date:May 27 2004 7:56am
Subject:Re: Foreign Key Constraints
View as plain text  
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

Thread
Foreign Key Constraintskyuubi27 May
  • Re: Foreign Key ConstraintsJeff Smelser27 May
  • Re: Foreign Key ConstraintsColin Bull27 May
  • Re: Foreign Key ConstraintsRobert J Taylor27 May
  • Re: Foreign Key ConstraintsMartijn Tonies27 May
    • Re: Foreign Key ConstraintsJeff Smelser27 May
  • Re: Foreign Key ConstraintsMartijn Tonies27 May
  • Re: Foreign Key ConstraintsMartijn Tonies28 May
    • Re: Foreign Key ConstraintsJeff Smelser28 May
  • Re: Foreign Key ConstraintsMartijn Tonies28 May
    • Re: Foreign Key ConstraintsMichael Stassen28 May
      • Re: Foreign Key ConstraintsMichael Stassen28 May
  • Re: Foreign Key ConstraintsMartijn Tonies28 May
    • Re: Foreign Key ConstraintsJeff Smelser28 May
    • Re: Foreign Key ConstraintsJeff Smelser28 May
  • Re: Foreign Key ConstraintsMartijn Tonies28 May
Re: Foreign Key ConstraintsSGreen27 May