I'm new to SQLite and SQL in general and I have a question about handling foreign key relationships when adding items to a database when the related items may not already exist. For example, if I have two tables: foods with "id", "name", and "type_id" columns and food_types with "id" and "name" columns. The "type_id" of the foods table relates to the "id" of the food_types table.
If I want to insert a new row in the foods table, but it has a food type that's not already in the food_types table, what's the best way to add it? Should I first insert a new row into the food_types table then use last_insert_rowid() to get the type_id to insert into the foods table? Sometimes the food_type will already exist so should I do a SELECT first to see if it exists and if not then do the insert (assuming all within a single transaction to avoid a race if another thread is also inserting)? This feels like the wrong way to do it. Or is there a better or more automated way to handle this? My database will have several related columns like this. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
