Message-ID: <3D910CEB.30704@houle.org>
Date: Tue, 24 Sep 2002 21:10:03 -0400
From: Kevin Houle <kevin@houle.org>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.1) Gecko/20020826
MIME-Version: 1.0
To: Ries van Twisk <ries@jongert.nl>
Subject: Re: Dublicates pairs in a table.
References: <000201c25d90$87230c10$6500000a@IT000>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 43
To: pgsql-sql@postgresql.org

Ries van Twisk wrote:

> I have a small question which I could not clearly find in the postgreSQL
> manual.
> 
> if I create this table and index
> CRAEATE TABLE test (
> 	id	SERIAL,
> 	c1	VARCHAR(32),
> 	c2	VARCHAR(32),
> 	c3	VARCHAR(32)
> );
> 
> CREATE UNIQUE INDEX test_idx ON test(id, c1,c2);
> 
> what I try to archive here is that I don't want duplicate pais in my table:
> example
> 
> INSET INTO test (c1,c2) VALUES('a', 'a');	-- Allowed
> INSET INTO test (c1,c2) VALUES('b', 'b');	-- Allowed
> INSET INTO test (c1,c2) VALUES('b', 'c');	-- Allowed
> INSET INTO test (c1,c2) VALUES('a', 'a');	-- Not allowed since we already
> have a duplicate ('a', 'a') pair
> INSET INTO test (c1,c2) VALUES('b', 'c');	-- Not allowed since we already
> have a duplicate ('b', 'c') pair
> 
> etc. etc. I think you get the idea...

I have the same issue with a table that currently holds well
over 600,000 rows. The case you left out is this:

  INSERT INTO test (c1,c2) VALUES('a','c');
  INSERT INTO test (c1,c2) VALUES('c','a');

I want that to fail, but I haven't been able to get it to fail
using unique indexes. I presume ordering is significant. Instead,
I am doing a SELECT prior to insert to insure the pair doesn't
already exist. If you've been able to get order-independent
pairs restricted to being unique using indexes, I'd like to know
about it. :-)

Kevin