Different ways to generate the latest int type primary foreign key in code

Question

I am new to sql. I have added 2 new tables in database. The primary key of first is a foreign key in the other. The type of the keys is integer. Now I want to generate the keys in the code and assign it to new data so that the association between different rows of the tables is right. How do I ensure uniqueness of keys and also get the latest key from the db so that there are no errors while saving.

If I had used guids then I would have assigned a new guid to the primary key and then assigned the same to the foreign key in the other table. Also there are multiple clients and one server which is saving the data.

The data to be inserted in both the tables is decided in the c# code and is not derived from the row inserted in the primary table. Even if get the id in db then also the relation between the rows should be stored in some form from the code because after that it is lost.

Answer 1

If you must generate keys on the clients, then you'll need some way to avoid collisions (two clients creating the same key). You can do this using GUIDs, because GUIDs are huge and the algorithms that generate them have been designed to minimise collisions. If you must use INTs, then you'll need to roll your own method, probably having each client reserve a block of IDs ahead of time, which is just going to be convoluted, so don't do that.

It's much easier to generate keys on the server, because it's always around and it knows what has and hasn't been used. In SQL Server, you can mark your id column with IDENTITY , to have SQL Server automatically generate an ID when you insert a record into that table. You can get the value that SQL Server used in the insert you just made using the @@IDENTITY function or the SCOPE_IDENTITY function. If you are using a stored procedure you can return the identity values as an output parameter or in the resultset, or you can SELECT SCOPE_IDENTITY() from your client.