Re: Auto increment

Both Date and Codd have conceded in published works that there are entirely valid reasons for considering surrogate and system-defined keys.

Codd wrote the following:

"There are three difficulties in employing user-controlled keys as permanent surrogates for entities.

(1) The actual values of user-controlled keys are determined by users and must therefore be subject to change by them (e.g. if two companies merge, the two employee databases might be combined with the result that some or all of the serial numbers might be changed.). (2) Two relations may have user-controlled keys defined on distinct domains (e.g. one uses social security, while the other uses employee serial numbers) and yet the entities denoted are the same. (3) It may be necessary to carry information about an entity either before it has been assigned a user-controlled key value or after it has ceased to have one (e.g. and applicant for a job and a retiree).

These difficulties have the important consequence that an equi-join on common key values may not yield the same result as a join on common entities. A solution - proposed in part [4] and more fully in [14] - is to introduce entity domains which contain system-assigned surrogates. Database users may cause the system to generate or delete a surrogate, but they have no control over its value, nor is its value ever displayed to them....." (Codd in ACM TODS, pp 409-410).

References

Codd, E. (1979), Extending the database relational model to capture more meaning. ACM Transactions on Database Systems, 4(4). pp. 397-434

Regards,

• Dan Guntermann

"Marshall Spight" <mspight_at_dnai.com> wrote in message news:RNvva.811178$L1.231301_at_sccrnsc02... > "Costin Cozianu" <c_cozianu_at_hotmail.com> wrote in message news:b9kodd$ka6pj$1_at_ID-152540.news.dfncis.de...
> > Marshall Spight wrote:
> > > "--CELKO--" <71062.1056_at_compuserve.com> wrote in message
news:c0d87ec0.0305101930.65eb8cca_at_posting.google.com...
> > >>
> > >>There are better ways of creating identifiers.
> > >
> > > But it seems pretty common that I just want to have a key,
> > > and I don't care what the specific values are (as long as
> > > they are unique,) and I'd like to have the system supply
> > > it for me. Is there no clean way to do this?
> > >
> > In the worst cass scenario, the whole set of columns is your key.
> > If the whole set of columns doesn't form a key, than you surely are
> > making a design mistake.

> >

>
> Ugh. So I have a table Persons with first name, last name, height, weight,
> DOB, and you want me to use that whole thing as a key? Not very appealing.
> What's wrong with a PersonId? Seriously, what's wrong with it?
>
>
> Marshall
>
>
>
>