Keeping Auto-Enter number sequential

[bbaliner]

Dear FM Pros,

This is probably a known problem: How to keep an Auto-Number nice and sequential even after deleting records?

I have this number as Primary Key in one of the files. After I delete a record, the number doesn't "slide", but keeps incrementing. So, if I deleted 1000 now the order is 999, 1001. That's uncool!

I can write a replace script everytime I delete a record, but am SCARED to mess with the Primary Key!! Is there any other way to do this?

Thank you, and keep your bikes well oiled.

[LeCates]

Hi bikeboy,

You neither need nor want to worry about the sequence of primary keys. Primary keys should follow the following rules:

1. Must exist

2. Must be unique

3. Never get deleted (unless record gets deleted)

4. Never change

5. Must be meaningless

Rule 5 is important because of the following reasoning -- If a key has meaning, that implies a domain of possible values for it. If there is a range of possible values, that implies the key could change. If the key changes, referential integrity is lost, and you create orphan records in your system. Functional dependency is lost. Database is broken, or at best confusing.

If you need a sequential serial number for some other reason, make it a regular attribute field -- not a primary key.

Good luck,

[Kurt Knippel]

See my articles on auto-entered keys for some ways to manage your primary keys.

http://www.fmforums.com/cgi-bin/ultimatebb.cgi?ubb=get_topic&f=8&t=000018

[bbaliner]

Thank you guys, you've been vety helpful.

I have one question for Kurt, though. Your PrimaryID_key connects various databases, correct? So they must match on this PrimaryID_key. But since the key is Status(CurrentTime), and can not be created simultaniously in both databases, it will not be the same. And therefore there will be no match. Am I being clear in my question?

In my present solution I have a key, that concatinates two meaningful fields that never change. That way I can make sure I am matching records that refer to the same person. How can I do that with you solution?

Please forgive my inexperience.

Thanks

[Kurt Knippel]

The Primary Key in any given database becomes the Foreign Key in it related databases. The Primary Key is ABSOLUTELY unique to each given file, and will in fact be unique across all of you databases.

You NEVER create the Primary->Foreign Keys seperately, and your never join two Primary Keys.

As Andy stated you NEVER want to use meaningful data as your Keys, for the reasons he mentioned.

What happens is that you create the Primary Key (parentID_key) in the Parent file, then when you create related records in the Child file, you then assign the value of the Parent's Primary Key into the newly created Child record's parentID_fkey, while still creating a new Primary Key (childID_key) for the Child record. This creates the relationship.