From: | roverr <roverr(at)dogpound(dot)is-a-geek(dot)org> |
---|---|
To: | Richard Huxton <dev(at)archonet(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Question: unique on multiple columns |
Date: | 2003-02-05 17:50:35 |
Message-ID: | 1044467435.13526.34.camel@junkyard |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, 2003-02-05 at 12:15, Richard Huxton wrote:
> On Wednesday 05 Feb 2003 2:47 pm, roverr wrote:
> > On Wed, 2003-02-05 at 09:09, will trillich wrote:
> > > you can
> > >
> > > create table something (
> > > a int4,
> > > b varchar(20),
> > > c timestamp
> > > );
> > > create unique index on something ( a, c );
> > > create unique index on something ( b, c, a );
> > >
> > > i don't understand your cols 4-9, tho. is this what you're
> > > looking for?
> >
> > Yes, thanks, thats what I was looking for.
> > Columns 4-9 are data that that corresponds to a unique
> > combination of b and c (and necessarily a).
> > Regards, Gary
>
> Note that a unique index on (a,c) necessarily implies unique combinations of
> (a,c,b) - since you can only have one (a,c) pair, there can only be one value
> for "b".
>
> In the case you described it looks like you have a redundant key.
> > > col 1: id, type serial, primary key
> > > col 2: host_id, type integer, foreign key to hosts table
> > > col 3: data_time, type timestamp
> > > col 4 - 9 data that is unique to col 2 and 3
>
> If col1=a,col2=b,col3=c you have unique(a), unique(b,c) if I understand what
> you're saying. You could drop "a" altogether and just use (b,c) as your
> primary key (since that key means something, unlike the serial).
Thank you Richard. I like your suggestion, I can do away
with an index and make it easy to reference the table with
an integer.
You've correctly determined and answered the
question I should've asked.
Regards, Gary
>
> If you reference this table a lot, you might want to keep "a" so you can refer
> to an integer rather than (varchar,timestamp).
>
> --
> Richard Huxton
From | Date | Subject | |
---|---|---|---|
Next Message | Marc G. Fournier | 2003-02-05 18:06:42 | PostgreSQL v7.3.2 Released |
Previous Message | Justin Clift | 2003-02-05 17:25:51 | Re: Is 7.3.2 out? |