Unanswered: How to insert values in db with referential integrity?
Hi. First post, nice to meet all of you.
I'm building an Access database that involves several tables linked to each other with referential integrity, because I understand that it's important to normalize tables.
But this makes writing an insert query more complicated. If it was just one table, I could run the insert and it would either succeed or get an error message if it violated a constraint. Instead, to add a new item I need to insert values into several tables, where each table could return an error.
For example, I have a table of Items, which has required foreign keys that link to tables of Locations and Categories. So if I have an item name, location, and category, and I want to use them to create a new Item, I have to:
-find or add values in Locations and Categories
-get the keys for the values I just got from Locations and Categories
-insert the Item name and keys into the Items table
So I need to do a sequence of inserts and selects with some if-then flow control. Does this require VBA (if so, I'm looking for a good tutorial), or can I do it with just SQL (if so, i'd like help figuring out the query)?
So if I have an item name, location, and category...
let's look just a little bit more closely at this statement
where would the location and category come from?
i'm betting some kind of form, yeah?
if you allow people to simply enter the location and category as text into text boxes, then, yes, you'd have to first look them up in their respective tables, find out if they already existed, and come back with their primary key values, which you will then use as foreign key values when you insert the item
however, if your users choose the location and category from dropdowns, then you know they already exist, and in fact you would've already populated the dropdowns from the location and category tables using PK values
Yes, I can set up a form with pre-populated dropdowns (although I'll need to learn how to do that), but I need to be able to do this for a lot of values.
My process has been:
1. start with an Excel spreadsheet with Item, Location, Category columns.
2. import it into Access as a table.
3. query Locations and Categories tables to make sure the values exist, and insert them if they don't, using a join from the import table.
4. insert the Item, Location, and Category into my main Items table using another join. I've normalized tables now so I will only have to insert the keys from Location and Category.
I'm trying to figure out how to have a single query accomplish steps 3 & 4.