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