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.
