Thread: 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
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
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/ !
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
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)
);
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
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
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/ !
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.
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/ !