Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2003
    Posts
    22

    Exclamation Unanswered: One to One relationship issue

    I have three different tables that I need to have the same client ID in. clientmain, clientdemographics, clientemcontact. I have these woring in Access XP just fine but when i recreated them in SQL 2000 I can't seem to get the cacade update to work right. I want to be able to add a record in client main and the clientID (autonumber) field should fill in the clientID fields in the other two tables. clientmain]![clientID] is my primary key, no duplicates and in other two tables I have clientID as the foreign key with no dups. In Access it will fill in the autonumber form the primary key to the foriegn but in SQL I can't get it work. I feel like such an idiot because I can't figure this out, please HELP.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: One to One relationship issue

    Use a triggers....

  3. #3
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Can you elaborate? DO you mean when you run an insert into table one that grabs a new clientID, you want that client ID to propagate to two other tables upon that insert? What other information would go in the other tables?

    The insert thing is where I am confused, if you meant for update or delete you could define this in your relationship contraint so that if you updated or deleted a PK it would update and or delete in any FK table.
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  4. #4
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    My question is for Brad, yes you could use triggers but would have to know what you want those triggers to insert (besides the ID)
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  5. #5
    Join Date
    Mar 2003
    Posts
    22
    Ok I'll look into a triggers, I've never used one before I'm still in the process of learning SQL 2000

  6. #6
    Join Date
    Mar 2003
    Posts
    22

    Unhappy

    Ok this is what I want, I want the client ID in [clientmain]![clientID] to propagate to [clientemcontacts]![clientID] and [clientemergey]![clientID]. I can get it to do that Access XP easily and I need to do the same in SQL. I have vb front for my SQL db and when I tell it to insert a new record I need that ID field to propagate into the two other tables to maintain a one to one relationship all of my other relationships are one to many and are working just fine. SQL should be able to do this in the relationships properties but it isn't working. clientmain is my PK and the other two are FK. The field properties are the same in each except I have PK set to auto number and I want that to propagate into the FK clientID fields. Seems simple until it doesn't work.

  7. #7
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Ok, I still have the same question, it is always possible I am just dense today.

    You have three tables

    [clientmain]
    [clientemcontacts]
    [clientemergey]

    And each only have one column?

    [clientID]

    If this is accurate, I don't know why you would do this, even if you have multiple columns on [clientmain] and only one column on the other two tables, still don't why you would have this but if I understand everything you are telling me you could do this.

    create trigger on [clientmain]
    for insert
    as
    insert into [clientemcontacts] select clientid from inserted
    insert into [clientemergey] select clientid from inserted

    If you do have multiple columns in those other two tables (which would make sense, hence my question) you would need to know what you want to place in the other columns besides the clientid column.

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  8. #8
    Join Date
    Mar 2003
    Posts
    22

    Talking

    Ok I do have more information in the other two tables not just an id field. I just decided to redesign the layout of the tables so that I don't have any one to one relationships. Thanks for your help guys.

  9. #9
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Sounds like the better choice, if you don't need them and it's not causing a problem...

    GL
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

Posting Permissions

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