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

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

pootle flump ur codings are working excelent.

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');

Yes. Or:
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

pootle flump ur codings are working excelent.

Coincidently - came across this just now:
http://www.sqlteam.com/item.asp?ItemID=319

pootle flump ur codings are working excelent.

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.

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.

pootle flump ur codings are working excelent.

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.....

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.

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.

pootle flump ur codings are working excelent.

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.

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.

pootle flump ur codings are working excelent.

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....

pootle flump ur codings are working excelent.