Horton

Thread

  1. Question: unique on multiple columns

    roverr <roverr@dogpound.is-a-geek.org> — 2003-02-05T11:46:35Z

    I'm running postgresql 7.2.1.
Is there a way to specify a constraint such that the combination
of two columns in a table is unique?

I have a table as follows:
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

I wish to have a constraint such that the combination
of the host_id entry in col 2 and each data_time entry
in col 3 must be unique.
My thoughts are to calculate a new column based on 
col 2 and 3 and force that to be unique.  Is this
the correct approach or is my table layout hopelessly
flawed?  

Thanks in advance,
Gary

  2. Re: Question: unique on multiple columns

    Arjen van der Meijden <acm@tweakers.net> — 2003-02-05T14:00:39Z

    How about a unique index.

Create unique index table_col2_col3_unique ON table (col2, col3);


> -----Oorspronkelijk bericht-----
> Van: pgsql-general-owner@postgresql.org 
> [mailto:pgsql-general-owner@postgresql.org] Namens roverr
> Verzonden: woensdag 5 februari 2003 12:47
> Aan: pgsql-general@postgresql.org
> Onderwerp: [GENERAL] Question: unique on multiple columns
> 
> 
> I'm running postgresql 7.2.1.
> Is there a way to specify a constraint such that the 
> combination of two columns in a table is unique?
> 
> I have a table as follows:
> 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
> 
> I wish to have a constraint such that the combination
> of the host_id entry in col 2 and each data_time entry
> in col 3 must be unique.
> My thoughts are to calculate a new column based on 
> col 2 and 3 and force that to be unique.  Is this
> the correct approach or is my table layout hopelessly
> flawed?  
> 
> Thanks in advance,
> Gary
> 
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
http://www.postgresql.org/users-lounge/docs/faq.html

  3. Re: Question: unique on multiple columns

    Will Trillich <will@serensoft.com> — 2003-02-05T14:09:13Z

    On Wed, Feb 05, 2003 at 06:46:35AM -0500, roverr wrote:
> I'm running postgresql 7.2.1.
> Is there a way to specify a constraint such that the combination
> of two columns in a table is unique?
> 
> I have a table as follows:
> 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
> 
> I wish to have a constraint such that the combination
> of the host_id entry in col 2 and each data_time entry
> in col 3 must be unique.
> My thoughts are to calculate a new column based on 
> col 2 and 3 and force that to be unique.  Is this
> the correct approach or is my table layout hopelessly
> flawed?  

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?

-- 
There are 10 kinds of people:
ones that get binary, and ones that don't.
 
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
 
Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !

  4. Re: Question: unique on multiple columns

    roverr <roverr@dogpound.is-a-geek.org> — 2003-02-05T14:47:54Z

    On Wed, 2003-02-05 at 09:09, will trillich wrote:
> On Wed, Feb 05, 2003 at 06:46:35AM -0500, roverr wrote:
<snip>
> > I have a table as follows:
> > 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
> > 

<snip>
> 
> 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
 
> -- 
> There are 10 kinds of people:
> ones that get binary, and ones that don't.
>  
> will@serensoft.com

  5. Re: Question: unique on multiple columns

    Bruno Wolff III <bruno@wolff.to> — 2003-02-05T16:53:09Z

    On Wed, Feb 05, 2003 at 08:09:13 -0600,
  will trillich <will@serensoft.com> wrote:
> On Wed, Feb 05, 2003 at 06:46:35AM -0500, roverr wrote:
> > I'm running postgresql 7.2.1.
> > Is there a way to specify a constraint such that the combination
> > of two columns in a table is unique?
> 
> 	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 );

While that will work, I think the following format is a little better.
 	create table something (
 		a int4,
 		b varchar(20),
 		c timestamp,
                unique(a,c),
                unique(b,c,a)
 	);

  6. Re: Question: unique on multiple columns

    Richard Huxton <dev@archonet.com> — 2003-02-05T17:15:13Z

    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

  7. Re: Question: unique on multiple columns

    roverr <roverr@dogpound.is-a-geek.org> — 2003-02-05T17:50:35Z

    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

  8. Re: Question: unique on multiple columns

    Will Trillich <will@serensoft.com> — 2003-02-06T16:33:51Z

    On Wed, Feb 05, 2003 at 10:53:09AM -0600, Bruno Wolff III wrote:
> On Wed, Feb 05, 2003 at 08:09:13 -0600,
>   will trillich <will@serensoft.com> wrote:
> > 	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 );
> 
> While that will work, I think the following format is a little better.
>  	create table something (
>  		a int4,
>  		b varchar(20),
>  		c timestamp,
>                 unique(a,c),
>                 unique(b,c,a)
>  	);

i like it. see what happens when you have to read the manual for
configuring apache and exim in the same week? it shoves out
previously-gained knowledge. :)

i also like specifying "primary key" at the bottom of the table
def. keeps commas after all the field defs, making it easier to
move whole lines around.

of course, my example is rather bogus -- as others pointed out,
unique(a,c) means that there'll only be 1 b for any a/c pair, so
the unique(b...) index is not-too-useful. it's just an
illustration, and a bad one at that. ah, well...

-- 
There are 10 kinds of people:
ones that get binary, and ones that don't.
 
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
 
Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !

  9. Re: Question: unique on multiple columns

    Bruno Wolff III <bruno@wolff.to> — 2003-02-06T21:25:53Z

    On Thu, Feb 06, 2003 at 10:33:51 -0600,
  will trillich <will@serensoft.com> wrote:
> 
> of course, my example is rather bogus -- as others pointed out,
> unique(a,c) means that there'll only be 1 b for any a/c pair, so
> the unique(b...) index is not-too-useful. it's just an
> illustration, and a bad one at that. ah, well...

If there are several different foreign key references into the table
using different combinations of a, b and c, you might need both indexes.

  10. Re: Question: unique on multiple columns

    Will Trillich <will@serensoft.com> — 2003-02-06T22:20:32Z

    On Thu, Feb 06, 2003 at 03:25:53PM -0600, Bruno Wolff III wrote:
> On Thu, Feb 06, 2003 at 10:33:51 -0600,
>   will trillich <will@serensoft.com> wrote:
> > 
> > of course, my example is rather bogus -- as others pointed out,
> > unique(a,c) means that there'll only be 1 b for any a/c pair, so
> > the unique(b...) index is not-too-useful. it's just an
> > illustration, and a bad one at that. ah, well...
> 
> If there are several different foreign key references into the table
> using different combinations of a, b and c, you might need both indexes.

yes, particularly if you have

	index ( a, b )
	index ( a, c )
	index ( b, c )

then any searching on field A or B will use an index; anything
using either with C will also use an index.

but in my fabricated example, something like

	index ( a, b )
	index ( b, a, c )

the second index doesn't add a whole lot more functionality than
just plain old

	index ( b )

would have.

-- 
There are 10 kinds of people:
ones that get binary, and ones that don't.
 
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
 
Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !