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
