Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2007
    Location
    Wichita, KS
    Posts
    7

    Unanswered: Primary Key in one-to-one relationship clears on edit

    I have two tables in a database that have a one-to-one relationship. For various reasons this is required; a single table is out of the question.

    Table 1 has the item id (PK), name, description, and a couple of other fields.
    Table 2 has the item id (PK), and review information. Table two will have zero or one record relating to the same item id.

    When a query with a left-join (Table1->Table2) is created, and any data is added to Table 2, the primary key (and only the primary key) is set to 0. Adding the record in Table 2 as a subdatasheet of Table 1 works without issue. The goal is, of course, editing the data in a form, but I need a query that works without clearing the PK to do so.

  2. #2
    Join Date
    May 2009
    Posts
    258
    I have had this same problem. The problem for me was that the ID in Table 1 was not an autonumber field, so the ID was reset when values were entered for Table 2. If it were an autonumber field, it would not be able to change the value of the Table 1 record ID.

    Regards,

    Ax

  3. #3
    Join Date
    Nov 2007
    Location
    Wichita, KS
    Posts
    7

    Exactly.

    Since the item IDs are already defined, I can't use an autonumber field, thus resulting in this issue.

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Do you need to add to Table2 before adding to Table1? If not, then you can set up Referential Integrity between the two tables so that the user cannot enter data into Table2 until data has been added to Table1.

  5. #5
    Join Date
    Nov 2007
    Location
    Wichita, KS
    Posts
    7
    Quote Originally Posted by DCKunkle View Post
    Do you need to add to Table2 before adding to Table1? If not, then you can set up Referential Integrity between the two tables so that the user cannot enter data into Table2 until data has been added to Table1.
    That's the thing, referential integrity exists and is enforced between the two. If I run a query like "SELECT Table1.id, Table2.id, Table2.Status FROM Table1 LEFT JOIN Table2 ON Table1.id=Table2.id", then edit the status column (if and only if nothing previously existed in Table2 for that id), both id columns will instantly change to "0".

  6. #6
    Join Date
    May 2009
    Posts
    258
    You have a few options that I see:
    1. Add Table2 records without joining to Table1.
    2. Even better would be to automatically add a record to Table2 for every record in Table1, so that it truly is one-to-one.
    3. Make sure you have Table2.id in the join and set that field to the Table1.id before anything else.
    4. Remove the relationship between the tables, remove id from the primary key of Table2, index it as "Yes (Duplicates OK)", then add a relationship back to the tables (it should be one-to-many now). You'll just need to use logic/query to make sure there is only at most one record in Table2 for each record in Table1.


    Ax

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you upload a database with the structure and the relevant query please? Ideally a bit of nonsense data too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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