Re: Foreign Key Constraints

[Michael Stassen]

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

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