[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
Re: [GENERAL] 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- x4294
-Original Message-
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
ow...@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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] simulate multiple primary keys
M == matthew.hart...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] simulate multiple primary keys
In response to Brandon Metcalf bran...@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/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] simulate multiple primary keys
--- On Thu, 7/2/09, Brandon Metcalf bran...@geronimoalloys.com wrote:
From: Brandon Metcalf bran...@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
