Re: [GENERAL] simulate multiple primary keys
--- On Thu, 7/2/09, Brandon Metcalf wrote: > From: Brandon Metcalf > 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
Re: [GENERAL] simulate multiple primary keys
In response to Brandon Metcalf : > 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
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
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
[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