The key has to be an integer (or numeric) field?
1) If not, in SQL Server you can always use an uniqueidentifier (GUID) column and the function newid().
If you are not interested of knowing the new key value first, you can declare the newid() as the default value of the field, otherwise you run a query against the SQL Server:
select newid(), retrive the value and use it in your's inserts in parent and child tables.
2) If the key has to be a numeric one, then use an identity enabled field as PK in the parent table, first insert the record in the parent table, then run this query against SQL: select @@identity (it's a global variable that holds the last given value for an identity column) and then use this value for the inserts in the child table.
Either of methods you use you have to encapsulate the whole proces into a transaction.
Good luck,
ionut
PS I would go with the first solution, because a GUID field is the best PK I've ever seen. The only restriction to that is when you have to sort on that field, it can not be done with an GUID field.
Quote:
Originally posted by tompoes
I mention something else.
Sometime i need first the Key.
For example , i call getID("TableName")
i use this key for many insert , parent / child relations.
But sometime i am not interested in the key en i only want to do a insert without the getID call
we have some tables with no single column key, we want to change that but not to rewrite the old application. The new application works whit the getid solution. So for old one it must be transparent.
|