Re: [GENERAL] select for insert?

Hi everyone,

say I have a table test
create table test (a int, b text);

and I want to ensure that 'b' is unique for a particular 'a' ('a' not being
unique).

Right now I can do something like
begin transaction
select count(*) from test where b =$value;
if count = 0 then insert into test (a,b) values ($a, $value);

But there is still a chance of a duplicate being inserted.

Is using a multicolumn unique index the best way to avoid such problems?
Any other suggestions?
Somehow I prefer to avoid getting exceptions from databases.

Is there a "select for insert" command?
e.g. while in transactions if a select for insert has the same criteria,
it's not executed until the other prior matching transactions are
completed. How yucky is that? <grin>

Thanks,

Link.