Hello, I am using mysql as database, innodb as tables engine. Some of my tables have foreign keys referencing to other tables primary keys.
Since the definition of foreign key is to restrict the value of itself, I would like to know if I should check the existence of the primary key in the parent table before inserting in the child table (therefore 2 queries: SELECT then INSERT). Or I should directly insert in the child table and mysql will do this for me (therefore 1 query with other hidden mysql job). Please note that performance is important for me. If any advantage/disadvantage applies let me know. Thank you, best regards

3 Years
Discussion Span
Last Post by diafol

If the "primary key" doesn't exist for the foreign key you enter, you should trigger an error, which you could trap - handling it gracefully.

SELECT then INSERT is usually not advisable as you may have concurrent access to your tables.

InnoDB should be able to check your constraints at the point of INSERT. 2p - but I'm no expert.

Edited by diafol

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.