unique constraint on 2 columns

Jonathan Vanasco <postgres@2xlp.com> writes:

I need a certain unique constraint in pg that i can't figure out.

Given:

create table test_a (
id serial ,
name_1 varchar(32) ,
name_2 varchar(32)
);

I need name_1 and name_2 to both be unique so that:
name_1 never appears in name_1 or name_2
name_2 never appears in name_2 or name_1

a standard 2 column unique index / constraint will not accomplish this.

But a trigger will...

--
Jorge Godoy <jgodoy@gmail.com>

This looks like more table design problem than
database limitation.
The one column should accommodate values from both
columns with unique index built on this column. Your
requirements tell me that these values are the same
nature and should be placed in the same column. To
distinguish between them use another column to put an
attribute.

--- Jorge Godoy <jgodoy@gmail.com> wrote:

On Apr 20, 2007, at 5:43 PM, Vladimir Zelinski wrote:

This looks like more table design problem than
database limitation.
The one column should accommodate values from both
columns with unique index built on this column. Your
requirements tell me that these values are the same
nature and should be placed in the same column. To
distinguish between them use another column to put an
attribute.

No, both values can be present at once. They're both external facing
guids that point to the same resource and serve as keys for the table
data. Some rows have one, some have two.

in regards to table desgin solution, if I redid anything it would be
something like:
table_main
main_id
table_main_2_guid
main_id
guid_id unique
context_id

but then i'm using 2 tables and have to join -- which means I need to
rewrite evertyhing that queries this table - which is both the core
table for my application and supposed to be free of any multi-table
queries for simple reads.

so i'm going to try the trigger route.

// Jonathan Vanasco

| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| SyndiClick.com
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| FindMeOn.com - The cure for Multiple Web Personality Disorder
| Web Identity Management and 3D Social Networking
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| RoadSound.com - Tools For Bands, Stuff For Fans
| Collaborative Online Management And Syndication Tools
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -

Jonathan Vanasco <postgres@2xlp.com> writes:

Given:
create table test_a (
id serial ,
name_1 varchar(32) ,
name_2 varchar(32)
);

I need name_1 and name_2 to both be unique so that:
name_1 never appears in name_1 or name_2
name_2 never appears in name_2 or name_1

I think you should consider redesigning your table layout. Whatever
kluge you come up with to enforce that is not going to be better than
changing the layout. You should put all the names into one unique
column and devise a different representation for whatever the
name_1 vs name_2 relationship is supposed to be.

regards, tom lane

On Fri, 2007-04-20 at 17:56 -0400, Jonathan Vanasco wrote:

On Apr 20, 2007, at 5:43 PM, Vladimir Zelinski wrote:

This looks like more table design problem than
database limitation.
The one column should accommodate values from both
columns with unique index built on this column. Your
requirements tell me that these values are the same
nature and should be placed in the same column. To
distinguish between them use another column to put an
attribute.

No, both values can be present at once. They're both external facing
guids that point to the same resource and serve as keys for the table
data. Some rows have one, some have two.

in regards to table desgin solution, if I redid anything it would be
something like:
table_main
main_id
table_main_2_guid
main_id
guid_id unique
context_id

but then i'm using 2 tables and have to join -- which means I need to

This is more correct structure, and yes, it would involve a join.

rewrite evertyhing that queries this table - which is both the core

No, it does not mean you need to rewrite anything. Use a view; that's
the great benefit you get from using a relational database like
PostgreSQL.

Regards,
Jeff Davis

On Apr 20, 2007, at 6:13 PM, Jeff Davis wrote:

This is more correct structure, and yes, it would involve a join.

I know thats the 'more correct' way -- but I can't do the join ,
which is why I posted about a 2 column unique index.
I tested with a join before posting - i have an already large table
that is growing quickly. in order to use the join and keep current
performance I'd need to scale out in hardware - which is just not an
option right now. searching 100M records vs searching 100M records +
a join is a huge difference. when you try to do analytics, its just
not appropriate in my situation.

No, it does not mean you need to rewrite anything. Use a view; that's
the great benefit you get from using a relational database like
PostgreSQL.

i'd have to rewrite everything that reads from that table to use the
view instead of the current query, and then worry about inserts.

On Fri, 2007-04-20 at 18:32 -0400, Jonathan Vanasco wrote:

On Apr 20, 2007, at 6:13 PM, Jeff Davis wrote:

This is more correct structure, and yes, it would involve a join.

I know thats the 'more correct' way -- but I can't do the join ,
which is why I posted about a 2 column unique index.
I tested with a join before posting - i have an already large table
that is growing quickly. in order to use the join and keep current
performance I'd need to scale out in hardware - which is just not an
option right now. searching 100M records vs searching 100M records +
a join is a huge difference. when you try to do analytics, its just
not appropriate in my situation.

If possible, try to separate performance issues from logical design
issues.

Try a materialized view:
http://www.jonathangardner.net/PostgreSQL/materialized_views/matviews.html

those often help with analytical queries.

No, it does not mean you need to rewrite anything. Use a view; that's
the great benefit you get from using a relational database like
PostgreSQL.

i'd have to rewrite everything that reads from that table to use the
view instead of the current query, and then worry about inserts.

Name the view the same thing as your table, and you won't have that
problem, and use rules to make the view updatable. PostgreSQL has
transactional DDL so you can rearrange the table's physical structure
without interrupting your application.

There are some situations when performance issues do affect logical
design and the ability to enforce constraints. PostgreSQL provides a lot
of tools to make those situations as rare as possible.

Hopefully PostgreSQL has enough capabilities to get you decent
performance, a logical design, and meet the requirements of your pre-
existing code. If not, you can use some of the hacks suggested by others
to allow you to enforce the constraint.

Hope this helps,
Jeff Davis

On Fri, 2007-04-20 at 15:52, Jonathan Vanasco wrote:

I need a certain unique constraint in pg that i can't figure out.

Given:

create table test_a (
id serial ,
name_1 varchar(32) ,
name_2 varchar(32)
);

I need name_1 and name_2 to both be unique so that:
name_1 never appears in name_1 or name_2
name_2 never appears in name_2 or name_1

After reading the responses, and pretty much agreeing that you should
probably redesign your table, I can think of a fairly good performing
yet simple solution.

create a new table, say test_names (name varchar(32) primary key);

Then you can FK name_1 and name_2 to test_names(name), although I'm not
sure that's stricly necessary for this exercise, it just sorta of feels
right.

Then create a trigger that that will insert / delete the matching
entries in test_names(name) each time you insert / update / delete from
test_a with both name_1 and name_2, and if an insert to test_names
fails, so that if the insert to test_a fails as well.

Seems like a giant hackish kluge, but it has the advantage of working
with your current schema and requiring no code changes. But keep in
mind, we have to live tomorrow with the hackish kludges we make today.