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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
