Re: Can't insert new record because of foreign keys

[Richard Gundersen]

Hi again

Thanks for the replies. I've tried the insert from my Java application, MySQL Control Center, and the command line, with the same error each time. I also checked the table types which are all InnoDB, and the types of the fields are also consistent (mediumint(9)).

I tried running SHOW INNODB STATUS but I just got an empty result set (from Control Center) so I'll try it again from the command line because I'm not sure of the syntax.

I think it has something to do with the fact that before I do the insert, I first delete any current associations in the ASSOC table e.g. :

DELETE FROM ASSOC WHERE resource_id = 3

and then I do the insert

INSERT INTO ASSOC (resource_id, attribute_defninition_id) VALUES (3,1)

...which then fails with the "cannot add or update....." error message. I think it may be something to do with "ON DELETE" settings on the foreign keys. I didn't specify any myself, but in MySQL Administrator, it seems to think they are defaulted to 'RESTRICT'.

Maybe I need to change this?