Re: Strategy for Primary Key Generation When Populating Table

On Feb 10, 2012, at 10:49, Vincent Veyron <vv(dot)lists(at)wanadoo(dot)fr> wrote:

> Le jeudi 09 février 2012 à 16:30 -0600, Merlin Moncure a écrit :
>
>> natural/surrogate is a performance/usability debate with various
>> tradeoffs. but using surrogate to 'create' uniqueness is a logical
>> design error; maybe a very forgivable one for various reasons, but the
>> point stands.
>
> Please consider the following case :
>
> I record insurance claims in the table below, where id_evenement,
> id_agent and date_origine define a unique event.
>
> However, records sometimes have to be canceled (set annule=true), and
> re-recorded the same way. They're normally canceled once, but
> occasionnally twice, or more (for various reasons).
>
> What would you use for a primary key?
>
> CREATE TABLE tbldossier (
> id_evenement text NOT NULL,
> id_agent integer NOT NULL,
> date_origine date NOT NULL,
> annule boolean DEFAULT false NOT NULL);
>
>

One possibility is to add a "version" field (integer) and combine evenement and version to create the unique. I'd also create a partial unique on evenement/annule to ensure you do not make more than one active version.

David J.