When to allocate primary key value?

[aharrisreid]

I have encountered a problem with my application framework and would appreciate input from other developers before deciding which way to go.

When allocating a unique identifier (or primary key, usually an integer value) to each new record, is it better to allocate a) immediately the record is created, or b) just before the new record is comitted to the table (eg. using TABLEUPDATE())?

As I see it, with method a) new child records can be added in same 'add' session, and have a parent id value that can be assigned immediately without having to save the parent record first. However, if the new record (perhaps child records as well) is discarded before saving, there will be a gap in the allocated number sequence (not that important IMO). Are there any pros/cons that I need to consider?

For the record I do not use the autoincrement feature in VFP8, I get the next available id for a particular field via a separate control table.

I would be interested to hear how other developers approach this issue.

Many thanks,
Alan

 

[craigsboyd]

I do it immediately via a stored procedure and call that procedure fron the default value for the primary key field...using a control table such as you do. Integer is almost always plenty big enough to outlast the program's lifetime even with gaps as you say.

http://www.sweetpotatosoftware.com

 

[aharrisreid]

Craig, thanks for the reply.

>I do it immediately via a stored procedure and call that
> procedure fron the default value for the primary key field

If you were using VFP as a front-end to SQL (using remote views), would you use a VFP stored procedure (in the .dbc), or a back-end procedure? Not being an expert with SQL syntax, I think I would be tempted to go for a VFP procedure for a more generic solution (but I bet there are good reasons why it's better to go with a SQL procedure!).

Regards,
Alan Harris-Reid

 

[craigber]

The PK is used to link records in related tables. There is no reason a user ever has to see the PK. In other words, it is internally used by the database. Because of that, the database should assign it. So, SQL Server should generate the PK.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"