constraints not registering

[jjjamie]

Hi all,

I am trying to create a database table that will act like a linked list by using self referencing. I have used the following definitions:

CREATE TABLE partslist(
id INT AUTO_INCREMENT,
PRIMARY KEY(id),
partid INT NOT NULL,
INDEX partid_ind (partid),
FOREIGN KEY (partid) REFERENCES parts(id),
nextpart INT NOT NULL -- Points to the next part in the list
) TYPE=INNODB;

INSERT partslist VALUES(1, 1, 1); -- dummy part (pointed to in order to end linked list)

ALTER TABLE partslist ADD INDEX nextpart_ind (nextpart);
ALTER TABLE partslist ADD FOREIGN KEY (nextpart) REFERENCES partslist(id);

-- Add some parts (starts with last part in list due to constraints)
INSERT partslist VALUES(2, 15, 1); -- nextpart is the dummy part
INSERT partslist VALUES(1, 20, 2); -- links to next part (2)


This works but the problem is with the constraints. For some reason I can do this:

INSERT partslist VALUES(3, 14, 99);

99 does not exist in the table. I should get an error!!


Does anyone have any idea why the constraints aren't registering?

Thanks in advance,

Jamie