Thread: unique constraint on 2 columns
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.
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: > 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> > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
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.