List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:May 28 2004 4:26pm
Subject:Re: Foreign Key Constraints
View as plain text  
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

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