>Hi Nancy,
>
>Frankly I have never figured out the purpose of surrogate primary keys. If you need a unique data order, a primary key is the way to go. If you don't need it, then stick with regular indices -- which are a lot easier to work with.
>
>I suspect I don't fully understand their (surrogate keys) utility. I'll have to get one of the wunderkinder to explain it to me sometime.
>
>Best wishes for a happy and successful new year,
>
>regards,

James,

In relational databse design every table MUST have a primary key (don't confuse this with a primary index in VFP). A primary key is defined as, "an attribute or group of attributes that uniquely and umabigmously identifies a specific instance of the entity." It is the interaction of Primary keys and foreign keys that allows tables data to be related to each other and produce information from data.

Primary keys can be found in one of two types, natural and surrogate. A Natural primary key is one in which the attributes that comprise the primary key would be required in teht albe even if they were not being used as the primary key, that is they are a natural attribute of the entity. A surrogate primary key is one that exist for the sole purpose of being the primary key and has no other meaning in the entity.

The benefits of surrogate primary keys are many, among them;

1) since the PK has no meaning the user will not desire to change it value therefore removing a whole bunch of referential integerity issues for the developer.

2) also teh meaningless nature of the surrogate key eliminates any need to reuse old deleted values, thus allowing the genration of a new key for every new record and eliminating the issue related to deleted records and the uniqueness of the Primary index.

3) since a surrogate key is always one field, there is no nedd to carry normalization beyond Boyce-Codd normal form because 4th and 5th only apply to mulitattribute primary keys.

4) by using the surrogate primary key you can relegate the issue of uniqueness of any candidate keys (like Invice number and the such) to business rules and not use the unique index types and theri implicit errors when uniqueness is violated.

This is 4 of a number of reasons to favor surrogate keys. There are many other reasons too.