From: Jeff Smelser
Date: May 28 2004 3:02pm
Subject: Re: Foreign Key Constraints
List-Archive: http://lists.mysql.com/mysql/166341
Message-Id: <200405281002.06950.tradergt@smelser.org>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

=2D----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Friday 28 May 2004 02:57 am, Martijn Tonies wrote:
> 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=3DInnoDB ROW_FORMAT=3Dfixed 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 S=
QL
> Server.
> Upscene Productions
> http://www.upscene.com

CREATE TABLE inno2 (
=A0 PK_Col =A0 =A0Integer NOT NULL DEFAULT 0,
=A0 Child_Col Integer,
=A0 PRIMARY KEY (PK_Col)
) TYPE=3DInnoDB ;

CREATE INDEX I_Inno2_ChildCol
=A0ON inno2(Child_Col);

CREATE TABLE inno3 (
=A0 PK_Col =A0 =A0Integer NOT NULL DEFAULT 0,
=A0 Child_Col Integer,
=A0 PRIMARY KEY (PK_Col)
) TYPE=3DInnoDB ;

ALTER TABLE inno3 ADD FOREIGN KEY (Pk_Col)
=A0 REFERENCES inno2 (Child_Col)
=A0 ON DELETE NO ACTION
=A0 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 thi=
s=20
more since as you can see, this worked and considering I do not have a id 2=
=20
or 3.. it should have failed.. so something isn't right.. The entire point=
=20
behind foreign keys is for constraints.. Its been awhile since I have done=
=20
foreign keys on mysql...

=2D --=20
	  Enough research will tend to support your theory.
=2D----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFAt1Ruld4MRA3gEwYRAvWEAKCcCqIsKLIPZk3od7Vn8z3rA9zAbACfZYhL
4VQLUYacl2HR9rmaBZC/pvw=3D
=3DyiUm
=2D----END PGP SIGNATURE-----