Although I have been programming for years, I usually do things more at the man-to-machine interface level and do not really have much experience with databases.
I have a master-detail relationship. To make it simple I will contrive an example.
A person might have many phone numbers. The records might look like this:
Master Table
integer PersonID
string PersonName
The index would be PersonID+PersonName since there may be more than one John Smith (Keep in mind this is contrived)
OK, so I gather the person’s name and insert the record. Then I need to get the PersonID out of the database so that I use it to insert his phone numbers.
What is the standard practice here? Right now I am using a sql statement where I get the MAX for PersonID, but this seems to be wrong on a few levels. [list=a]
[li]On a server there may be many people inserting at the same time[/li][li]There will come a day when you run out of numbers and it rolls over[/li][li]The records might be cached and we might not get the correct PersonID[/li][li]Other reasons I may not have thought of.[/li][/list]
How does one usually insert records into master-detail tables?
What DBMS are you using? Each handles this situation slightly differently.
If it’s SQL Server, the way to do it would be to use the integer value as the primary key and forget the id+name idea (what does that add, anyway?). You would also make that field an identity field, which basically means that every time you do an insert it will automatically use the next biggest #.
Once you have it set up like that, the variable @@identity always is set to the last inserted key value. Once you have that, you can use it in a variety of ways to insert to the child table - typically you return it from the first SQL Insert and use it in the second, or write a stored procedure that would use it to do the master insert then the child insert.
A pain in the ass, IMO. I’m much more happy using uniqueidentifiers (GUIDs) as primary keys. But that’s a religious debate
