Re: Dublicates pairs in a table.

Richard,

do you suggest using a stored procedure to handle this? I do expect that the
table will be large (for me large is a around 10000-20000 records, the table
as more columns but I only need the restriction on c1 & c2) but I don’t
expect lots of inserts and deletes. Data entry will be done using a regular
user but of course a update needs to be done within a acceptable time so the
user does not get annoyed.

best regards,
Ries van Twisk

> -----Oorspronkelijk bericht-----
> Van: Richard Huxton [mailto:dev(at)archonet(dot)com]
> Verzonden: maandag 16 september 2002 17:08
> Aan: ries(at)jongert(dot)nl; pgsql-sql(at)postgresql(dot)org
> Onderwerp: Re: [SQL] 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
>