RecordID vs. Generating a serial number

[James Gill]

I've run into a solution where the previous developer has chosen to use an auto-enter calculated value of get (RecordID) as the primary key field instead of the (to me) more traditional auto-enter serial number option.

After looking at the solution in more detail, to me it appears as if this option has a more dynamic solution (With obvious caveats, ie - not able to define "human friendly" pk fields and ODBC limitations are two examples that come to mind) especially when it comes to performing data syncing imports.

Are there any huge, show stopping limitations when it comes to using get (RecordID) as a primary key value that I don't know of? Is it slow/faster than using serial numbers or text fields as the primary key value? Are there any white papers out there that filemaker has made that explains this in more detail?

Edit: I think I just found a show stopper. get (RecordID) will always display the next higher value of the table, regardless of how many records were in the table previously. For example, if I have 40 values in a table with a primary key field set with the auto enter serial value checked, it will always go up by one, regardless of how many records I add or delete. If I have a field set to generate get (RecordID) it will only set that field as whatever number the record represents in the current table.

Say If I add five new records into the table:

Record  Result for get (RecordID) Result for next serial

1                    1                    1

2                    2                    2

3                    3                    3

4                    4                    4

5                    5                    5

Then I delete two of them

Record  Result for get (RecordID) Result for next serial

1                    1                    1

2                    2                    2

3                    3                    3

and add two more values

Record  Result for get (RecordID) Result for next serial

1                    1                    1

2                    2                    2

3                    3                    3

4                    4                    6         

5                    5                    7

I believe that qualifies as a show stopper. If ever had to archive values out of a main table and go back and reference them, there wouldn't be a real primary value (unique) since the record ID would be reclaimed if it was ever deleted. Is that correct?

Edited November 7, 2010 by Guest

[comment]

Are there any huge, game-stopping limitations

Yes, I think there are: RecordIDs are file-specific and you have no control over them. If you ever decide to move your data to a new file, you'll have a major headache.

[comment]

record ID would be reclaimed if it was ever deleted. Is that correct?

No, it isn't. I suspect you were looking at Get (RecordNumber) instead.