Re: unique constraint on 2 columns

[Jeff Davis <pgsql(at)j-davis(dot)com>]

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