List:General Discussion« Previous MessageNext Message »
From:Jeff Smelser Date:May 28 2004 3:02pm
Subject:Re: Foreign Key Constraints
View as plain text  
-----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=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

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...

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

iD8DBQFAt1Ruld4MRA3gEwYRAvWEAKCcCqIsKLIPZk3od7Vn8z3rA9zAbACfZYhL
4VQLUYacl2HR9rmaBZC/pvw=
=yiUm
-----END PGP SIGNATURE-----
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