Member Avatar for cmps

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

  • 2 Contributors
  • 1 Reply
  • 205 Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by diafol
Member Avatar for 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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.