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

> > Martijn Tonies wrote:
> >
> >> Hi Jeff,
> >>
> > <snip>
> >
> > In this example, inno3.PK_Col references inno2.Child_Col, so the 2nd and
> > 3rd statements are failing because they try to set inno3.PK_Col to
> > values not present in inno2.Child_Col.  The NULLs are irrelevant.

Woops, right Michael - got that one wrong.

> > Michael
>
> Perhaps this is what you meant?
>
> 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 ;
>
> CREATE INDEX I_Inno3_ChildCol
>   ON inno3(Child_Col);
>
> ALTER TABLE inno3 ADD FOREIGN KEY (Child_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);
>
> mysql> SELECT * FROM inno3;
> +--------+-----------+
> | PK_Col | Child_Col |
> +--------+-----------+
> |      1 |      NULL |
> |      2 |      NULL |
> |      3 |      NULL |
> +--------+-----------+
> 3 rows in set (0.05 sec)

This makes perfectly sense.

So, once again I dare to ask: what's the problem with NULLable
Foreign Keys? It works fine :-)

(now, who was it that said that FKs should be entered/exist
always?)

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