If I insert something into a PK field (say '1'), and then do the same
in another transaction context (without committing the first). It
will fail, yes?

Presumably the same is true of other unique indexes or constraints -
they can 'see through' the transaction context?

Is there a way of preventing this from happening (maybe not with PK's
but with indexes?)

Sometimes I use a table as working storage for a sproc, and use
transacton isolation to prevent one instance seeing data from
another. When each instance is finnished they 'delete from mytable'
so the committed state of the table is always empty. (I also empty
the table at the start of the sproc, just to be sure!)

Good idea? I can't use PK's because these see through the context, so
if I must maintain uniqueness I usually have a trigger:

before insert trigger:
select count(*) from mytable where pk = :new.pk into :a;
if (a > 0) then
exception key_viol;

Which seems a bit inefficient...

I suppose I could use a non-unique index to make the trigger
faster.. ;)

Thanks,

John