Re: Question: unique on multiple columns

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

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