Re: Strategy for Primary Key Generation When Populating Table

On Feb 10, 2012, at 10:49, Vincent Veyron <vv.lists@wanadoo.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
createa partial unique on evenement/annule to ensure you do not make more than one active version. 

David J.