Adding a database record with foreign key

Question

Let's say there is a database with two tables: one customer table and one country table. Each customer row contains (among other things) a countryId foreign key. Let's also assume that we are populating the database from a data file (i.e., it is not an operator that is selecting a country from a UI).

What is the best practice for this?

Should one query the database first and get all ID's for all countries, and then just supply the (now known) country id's in the insert query? This is not a problem for my 'country' example, but what if there is a large number of records in the table that is being referred?
Or should the insert query use a sub query to get the country id based on the country name? If so, what if the record for the country does not exist yet and has to be added?
Or another approach? Or does it depend? :)

Laurent S. · Accepted Answer · 2013-05-14 15:55:13Z

1

I would suggest using a join in your insert query to get the country id based on the country name. However, I don't know if that's something possible with every SGBD and you don't give more precision on the one you're using.

answered May 14, 2013 at 15:55

Laurent S.

6,9373 gold badges32 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jimmy Over a year ago

Using mysql. What happens if a record for the country does not already exist? Thank you.

Laurent S. Over a year ago

well I would be tempted to say you just have to populate your country table first... but then when speaking about other data it wouldn't be correct. In that case a more programmatic approach would be needed : testing if value exist, if not insert it, then use the new id to populate field in main table.

Collectives™ on Stack Overflow

Adding a database record with foreign key

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related