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

ok - I've checked.

> > > 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,
> >
> > Its a foreign key, you can not null foreign keys.. Thats the problem.

That's not true. Here's what I got:

CREATE TABLE inno1 (
  PK_Col    Integer NOT NULL DEFAULT 0,
  Child_Col Integer,
  PRIMARY KEY (
    PK_Col
  )
) TYPE=InnoDB ROW_FORMAT=fixed DEFAULT;

CREATE INDEX I_Inno1_ChildCol
 ON inno1(Child_Col);

ALTER TABLE inno1 ADD FOREIGN KEY (Child_Col)
  REFERENCES inno1 (PK_Col)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;


After that, I inserted data:
INSERT INTO inno1(PK_Col, Child_Col) VALUES (1, NULL);
INSERT INTO inno1(PK_Col, Child_Col) VALUES (2, NULL);
INSERT INTO inno1(PK_Col, Child_Col) VALUES (3, 1);


Now, can someone explain what the problem with NULLable FKs is?

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