Re: simulate multiple primary keys

In response to Brandon Metcalf <brandon@geronimoalloys.com>:

> 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?

Your primary key can span multiple columns, i.e.
PRIMARY KEY(jobclock_id, employee_id, machine_id)
Could be more columns.

Keep in mind that this ensures that the combination of all those
columns is unique, which may or may not be what you want.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/