simulate multiple primary keys

Just create a unique constraint on all of the columns.

Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital
(613) 549-6666 x4294

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Brandon Metcalf
Sent: Thursday, July 02, 2009 1:28 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] simulate multiple primary keys

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

.now.

M == Matthew.Hartman@krcc.on.ca writes:

M> Just create a unique constraint on all of the columns.

Ah. Didn't realize you could specify more than one column as part of
a unique constraint.

Thanks.

--
Brandon

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/

w == wmoran@potentialtech.com writes:

...

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

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

Sure. I realize that a primary key can be made up of multiple
columns, but a unique constraint across the columns I need as
suggested by Matthew is what I was looking for.

Thanks.

--
Brandon

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