Walter,

Further there are problems when you want to insert parent and child records in one transaction. Since you don't know which value SQL server will assign to the PK of the parent, you won't know what value to assign to the FK in the child table. Personally I use a Newkey stored procedure to generate new keys. While identity columns seem an easy choice at first sight, it might catch you later when things start to get a little more complex.

I handle this in a stored procedure - I insert into the parent table, grab the PK that SQL Server assigned by using Scope_Identity, and then use that PK to write out the FKs in the child rows. Haven't run into an issue. What types of problems do you mean?

Kevin