Re: unique constraint on 2 columns

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.