Обсуждение: 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
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