StrataFrame Forum

Primary Key Value?

http://forum.strataframe.net/Topic6193.aspx

By Ben Hayat - 1/24/2007

All my years of database programming, I had worked directly with RDBMS. This new switch over to .Net and dealing with ROM seems to require new strategy in some areas.



I had always maintained the primary key value, using an Integer field and maintained the incremental myself regardless which database engine I was using. This way I could switch DB and things would be the same.



My question is, to have unique Primary keys, can I still use the same techniques as I have before or are there other approaches that would be better when working in Object Relational Mapping environment? Perhaps I've missed it in docs, is there a pattern in SF that I should follow to maintain a safe position, especially when linking to other tables?



Thank you in advance!
By Greg McGuffey - 1/24/2007

Hi Ben! I'm just another user like you, but I might be able to get you started.



In other posts I've read that this sort of thing is done in the SetDefaultValues event handler. The sub is created automatically when you create a BO (though is has no code in it). Here you would handle whatever logic you needed to create your PK. Also be sure to tell the BO that you are handling PK by setting the PrimaryKeyIsAutoIncremented property to false.



I'll be interested to see what Ben (Chase) or Trent have to say, to see if I'm close at all BigGrin
By Ben Hayat - 1/24/2007

Thanks Greg;



Yes, I had already read the other post where Ben mentioned in what event we need to take care of it. But my main focus was to ask anyone (especially SF team) who has more experience in OO database programming than I do, if there are special strategies in SF for incrementing PK or I can use the old fashion way of doing it myself.



Another point would be to use auto increment using DB engine. In the past I have been bitten by this, where a table had to be restructured/indexed and the database re created the PK again (with different numbers) and it broke all the links in other tables. It's almost like I don't trust it.



Can someone tell me how solid is the auto increment in SQL2005?
By Greg McGuffey - 1/24/2007

Ah. Sorry I wasn't more help. The only time I've had trouble with auto-increment PK is when I have had to move data from one db to another, especially if there are child tables and associated records. If the new table(s) is(are) empty, this is pretty easy as you can tell SQL Server to NOT create the PK, but instead use the one you are loading. If the tables aren't empty (you are merging data), this can be a pain, though I've done it several times. If you are moving data around a lot, I'd avoid auto-incrementing PKs. Just my $0.02.
By StrataFrame Team - 1/25/2007

Greg is right... if you want to assign your own primary keys, you can Set the PrimaryKeyIsAutoIncremented to False and specify the PK value within the SetDefaultValues event handler.

However, I would just let the DB assign the PKs.  Our medical software used to assign it's own PKs.  We had a database table that stored the next PK value for each of the tables in the database.  However, we did this because the system was designed in the old days of VFP when there was no auto increment option for the PK.  We wish we never had to do it.  Maintaining that file was the biggest pain the the rear ever.

Nowdays, you can't find a database in it's current version that doesn't support auto incrementing PKs.  It's much easier to just let the database handle it.

By Ben Hayat - 1/25/2007

Thanks Greg & Ben;



However, we did this because the system was designed in the old days of VFP when there was no auto increment option for the PK. We wish we never had to do it. Maintaining that file was the biggest pain the the rear ever.




I agree with you 100%! I had it the same way too where I had another filer that each record had two fields (FileName, Last_ID). And somtimes in heavy multi user, due to serveral people hitting the "increament file" at the same time, I would see my "Next_ID" would skip or worst, two records get the same number.



And that's why I post these questions to avoid the old pitfalls.

Thanks!