Re: Dublicates pairs in a table.

On Monday 16 Sep 2002 3:51 pm, Ries van Twisk wrote:
> Dear guys/girls,
>
> 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);

Close, try

CREATE UNIQUE INDEX test_idx ON test (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');    -- Not allowed since we already
> have a duplicate ('a', 'a') pair

> What I want to know is that if this is smart do do, or is there a other
> better way to make sure I don't insert duplicate pairs in my database.
> I'm not sure if a stored procedure is better in my case since I don't
> really need the index on columns c1 or c2.

Unless test is a very small table with lots of inserts/deletions I'd just use
the index, otherwise you'll have to scan the table and check for another copy
anyway.

HTH

- Richard Huxton