List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:May 28 2004 3:42pm
Subject:Re: Foreign Key Constraints
View as plain text  
Hi Jeff,

> > 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?
>
> CREATE TABLE inno2 (
> PK_Col Integer NOT NULL DEFAULT 0,
> Child_Col Integer,
> PRIMARY KEY (PK_Col)
> ) TYPE=InnoDB ;
>
> CREATE INDEX I_Inno2_ChildCol
> ON inno2(Child_Col);
>
> CREATE TABLE inno3 (
> PK_Col Integer NOT NULL DEFAULT 0,
> Child_Col Integer,
> PRIMARY KEY (PK_Col)
> ) TYPE=InnoDB ;
>
> ALTER TABLE inno3 ADD FOREIGN KEY (Pk_Col)
> REFERENCES inno2 (Child_Col)
> ON DELETE NO ACTION
> ON UPDATE NO ACTION;
>
> INSERT INTO inno2(PK_Col, Child_Col) VALUES (1, NULL);
> INSERT INTO inno2(PK_Col, Child_Col) VALUES (2, NULL);
> INSERT INTO inno2(PK_Col, Child_Col) VALUES (3, 1);
>
> INSERT INTO inno3(PK_Col, Child_Col) VALUES (1, NULL);
> INSERT INTO inno3(PK_Col, Child_Col) VALUES (2, NULL);
> INSERT INTO inno3(PK_Col, Child_Col) VALUES (3, NULL);
>
> select *
>    from inno2;
>
> The actual way he was doing it was above.. I am going to have look into
this
> more since as you can see, this worked and considering I do not have a id
2
> or 3.. it should have failed.. so something isn't right.. The entire point
> behind foreign keys is for constraints.. Its been awhile since I have done
> foreign keys on mysql...

Indeed, inserting (2, NULL) fails - while it shouldn't. A FK should only be
enforced if there's a value. Not when it's NULL (for the FK columns). This
is true for all other database engines that I know.

How else can you create tables with either a relationship to another table
or no relationship?

IMO, all INSERTs you wrote should succeed.

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