List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:May 27 2004 8:00am
Subject:Re: Foreign Key Constraints
View as plain text  
Hi,

> On Wednesday 26 May 2004 11:22 pm, 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.
>
> I am not to sure how its possible to fix it.. Thinking about this.. I
would
> have a bookid which is never null. Considering no matter if the book is
out
> or not, you have that book. I then would have a borrow table, lets say, as
> the foreign key would be bookid.. I date borrowed, and date returned would
be
> how I would know if its out or not..
>
> Just thinking off the top of my head as there probably is a better way to
do
> it.
>
> I am sure there are other ways to do this.. Foreign keys can't be null.

Why not? What's wrong with this:

BORROWER
BorrowerID

BOOKS
BookID
BorrowerID (nullable)

FK from Books.BorrowerID to Borrower.BorrowerID

I haven't checked, but this _should_ be possible.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com

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