Re: Foreign Key Constraints

[Michael Stassen]

Martijn Tonies wrote:
Hi Jeff,
<snip>
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
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.

Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]