foreign key constraint

Question

I'm having trouble editing the data in mysql with foreigh keys. When I try to update a foreign key, it says:

Cannot add or update a child row: a foreign key constraint fails (sadsystem/products, CONSTRAINT fk_ProductRelationship11 FOREIGN KEY (size_id) REFERENCES product_sizes (size_id))

Answer 1

The standard way of doing this is SET CONSTRAINTS DEFERRED.

However, MySQL/InnoDB does not support this. You will have to edit your data in consistency order: that is, you'll first have to create the target row in product_sizes before you can refer to its size_id from other tables.

(If you're sure you know what you're doing (such as when importing data), you can also temporarily disable the foreign_key_checks setting, but this does not check referential integrity when you re-enable it: any constraints broken while it was disabled will stay broken.)

Answer 2

When you update a column which is part (or all) of a foreign key, then the new value of the complete foreign key must reference a row in the referenced table. The error message is saying you are attempting to update the foreign key to a value that does not exist in the referenced table.