List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:May 28 2004 4:44pm
Subject:Re: Foreign Key Constraints
View as plain text  

Michael Stassen wrote:

> 
> 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.
> 
> 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)

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