Re: simulate multiple primary keys

--- On Thu, 7/2/09, Brandon Metcalf <brandon(at)geronimoalloys(dot)com> wrote:

> From: Brandon Metcalf <brandon(at)geronimoalloys(dot)com>
> Subject: [GENERAL] simulate multiple primary keys
> To: pgsql-general(at)postgresql(dot)org
> Date: Thursday, July 2, 2009, 5:27 PM
> I have the following table:
>
>   gms=> \d jobclock
>                
>                
>               Table
> "public.jobclock"
>      Column    |   
>           Type     
>         |       
>                
>    Modifiers
>  
> -------------+--------------------------------+----------------------------------------------------------------
>    jobclock_id | integer     
>                
>   | not null default
> nextval('jobclock_jobclock_id_seq'::regclass)
>    employee_id | integer     
>                
>   | not null
>    machine_id  | character
> varying(4)           |
> not null
>    workorder   | character
> varying(8)           |
> not null
>    operation   |
> integer             
>           | not null
>    bartype     |
> character varying(10)          |
> not null
>    clockin     |
> timestamp(0) without time zone | not null
>    clockout    | timestamp(0)
> without time zone | default NULL::timestamp without time
> zone
>    comments    | character
> varying(255)         | default
> NULL::character varying
>   Indexes:
>       "jobclock_pkey" PRIMARY KEY, btree
> (jobclock_id)
>   ...
>
> I need to keep jobclock_id unique and not null, but I also
> need to
> ensure that no row is duplicated.  Is my best bet to
> drop the current
> primary key and make a primary key out of the columns that
> I want to
> ensure remain unique from row to row?
>
> Thanks.
>
>
> --
> Brandon
>
> --

It depends of a lot of variables, take two stage:

Stage 1
one employee_id can use some machine_id create you can create a unique key to employee_id

Stage 2
one employee_id can use one machine_id create you can create a unique key to employee_id,machine_id

is a example, but i think you have to normalize the table