Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2011
    Posts
    11

    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)?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sigil View Post
    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

    make more sense now?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Posts
    11
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sigil View Post
    I'm trying to figure out how to have a single query accomplish steps 3 & 4.
    a single query cannot do all of that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2011
    Posts
    11
    Ok, I'll learn how to do it in VBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •