Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2006
    Posts
    36

    Question Unanswered: One-to-one relational INSERT

    I have two tables related one-to-one.
    A(PK,a,b,c);
    B(PK,x,y,z);

    (A.PK -> B.PK)

    I have performed an insert on table A and my auto PK has been generated. I would like to now insert into table B , however when I insert I get the error "INSERT statement conflicted with FOREIGN KEY constraint bla bla.." as for every record in A there must be one in B.

    eg: -> insert into A values (1,2,3); PK is autogenerated, lets say '25'
    -> insert into B values(5,6,7) .....?????? Error;

    How do I retrieve or link the PK ('25') from table 'A' to insert into table 'B' without providing an explicit identity key when inserting for the same record.
    What would the insert statement be?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi groover

    the answer is - you can't. At some point you need to know the PK value, either coded explicitly in the values or obtained using a select statement.... but with the pk value in the WHERE clause.

    There are implimentation level solutions but no pure SQL solution.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2006
    Posts
    36
    Does that mean that I would get the PK from table A using some select statement and then do an explicit insert into table B?

    eg: Declare @PK = select PK from A where A.a= 'whatever';
    insert into B values (@PK,'bla','bla','bla');

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes. Or:
    Code:
    INSERT INTO B 
    SELECT PK,'bla','bla','bla'
    FROM A
    WHERE A.a = 'whatever'
    Another option is not to have 1 to 1 relationships if design allows.

    If you are using SQL Server (but this is an ANSI forum !) also check out @@Identity in Bol.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Coincidently - came across this just now:
    http://www.sqlteam.com/item.asp?ItemID=319
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Mar 2006
    Posts
    36
    I know this is more design related, but my one-to-one relationship is formed on the basis that table A exists and a row in B will only exist if A has an instance of B.

    Example:
    Table A is a Team of football players.
    Table B is a Manager table which stores the manager's of team's details IF the team has a manager. A manager should be able to be added at a later stage, hence the original question about inserting into B. Is this the right relationship to have or should I have an identity column in Table B and a foreign key to Table A in Table B? This would eliminate the Identity issue.
    My only issue then, is that this creates a one-to-many relationship if I do this. Is that bad? How do I enforce that any team only has 1 manager if I would use the new one-to-many relationship.
    Last edited by grooverinthesouth; 05-09-06 at 07:59.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You need to ask yourself these questions:
    Can a team have n managers (a la Liverpool in the nineties)?
    Do you want a historical record of a team's managers?

    If the answer either of these is yes then you are looking at a one to many relationship.

    If it is no to both then you are looking at a one to one relationship. In this case - why not just have a Manager field(s) in table A and leave it NULL if there is no manager and dump table B?

    BTW - the identity question is all to do with physical design whereas the above is logical. Check out surrogate vs natural keys on google for starters if you want to learn more. For my own two penneth - how many Liverpool teams will there be? Only one yes? So dump the identity and set the team name as a natural PK. At the minimum ensure that team name has a unique, not null constraint if you stick with identity PKs.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    As Pootle helpfully suggests there is no way to do what you want in an ANSI / ISO SQL environment, however you could do what you want using a stored procedure or trigger. effectively you call the procedure or trigger with all the data you need to populate your tables. In the circumstances you outline your procedure / trigger will write the first values, extract the identity value and then write to subsequent tables using the identity.

    it should work on all major SQL server based products inculding MySQL, SQL Server, Sybase et al.....
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2006
    Posts
    36
    It is definitely a one-to-one relationship then, based on your questions pootle.
    I agree with your solution, however Table B (Managers) would have related tables to it. for example, a one-to-many address table, a one-to-many hobbies table etc. These would all then be referenced back to Table A's PK if I dropped Table B if I'm not mistaken. The issue is that I have +-15 other tables that have the same characteristics and relationships as Table B (ie. 0 or 1 instances and related tables) and if none of them exist(ie. no instances of the other tables), Table A would be full of Null values. This is the reason I have split them up; to remove redundancy.

    Is it correct to have the identity(PK) of the instance of a manager stored in Table A as a field to form the relationship to Table B from Table A. That way, if there is no instance of a manager, the 'foreign key' field in table A will be null.
    Last edited by grooverinthesouth; 05-09-06 at 09:09.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It depends on whether you consider the manager an entity in and of itself or as an attribute of the team. In this case - I see it as an attribute. If you had n managers or kept a history then the manager is an entity but not, to my mind, in this case. My problem with 1 to 1s of this type is that you can extend the argument to an absurd extreme. Say your columns are Manager_FirstName, Manager_SurName, Manager_MiddleName. But the manager might not have a middle name. Do you create a middle name table to reduce redundancy?

    Actually - reread - do you really track a manager's hobbies but not the team's management history? Anyway - it is now an entity. Your solution is sound.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Mar 2006
    Posts
    36
    Forigve me, I have one last question...

    In the one-to-one scenario described above, am I correct in saying that
    Table A's primary key (PK) will be an identity field and Table B's PK will not?
    This seems obvious to me, but considering all the light thats been shed, I want to make sure...

    Thanks for all the input guys.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Totally spot on (kind of)...

    IF you use identities then you are absolutely correct and have avoided a mistake many others make.

    You can, of course, not use identities at all and use a natural key in both tables instead. But that's just me banging an old worn out drum.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    But that's just me banging an old worn out drum.
    if it makes you feel any better, you are not alone
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    if it makes you feel any better, you are not alone
    It does Rudy - thankyou I had to take 5 minutes to lie down.

    I was going to post (and now I think I will) the following:
    Write two queries that answer the following question (one for a db design with nothing but identity PKs and one with only natural keys):
    Which teams have a manager whose hobbies include poker?

    One query uses four tables. One requires only two. And that just to answer a very simple request. Groover - if you want some terribly exciting links on this subject please let me know. Rudy even hosts some of them on his site....
    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
  •