Re: Sequences as primary key experience?

Simon Griffiths wrote:

> Jackie Marino wrote:
>
> > Does anyone have any advice/experience on using sequences as primary
> > keys? We are going to use them for almost all of our tables and would
> > like to know any potential problems.
>

> If the table already has a clear primary key, then why not use it ??
> Any additional
> column you add takes up space.
>

It is all to common for a primary key to eventually become non-primary. See http://www.ambysoft.com/mappingObjects.html for some reasons. Also, sequence generated index keys will often be smaller resulting in smaller and faster indexing and joins.

> On a purely practical viewpoint, a database which consists primarily of
> surrogate
> keys is much more difficult for people new to the schema to understand.
>
> You also make 'short-cut joins' much more difficult .
> eg customer, order, order-line
> to get all order-lines for a customer means a three-table join. If the
> key of
> order-line is defined as cust-id, ord_id, ord_line_no then the primary
> key
> index of the order line immediatly gives the required rows with no joins.
>
> Simon.
Received on Thu Jul 02 1998 - 17:51:48 CDT