--- On Thu, 7/2/09, Brandon Metcalf <brandon@geronimoalloys.com> wrote:
> From: Brandon Metcalf <brandon@geronimoalloys.com>
> Subject: [GENERAL] simulate multiple primary keys
> To: pgsql-general@postgresql.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