On Sat, Jul 06, 2002 at 12:18:56PM -0300, wrote:
>
> When you are trying to insert a row in a table with a UNIQUE constraint,
> unless it already exists, you can try something like:
>
> INSERT INTO table (id) SELECT val WHERE NOT EXISTS (SELECT 1 FROM table WHERE id =
>val)
>
> However, this does not work as expected if another backend inserts a row
> with the same unique column(s).
>
The same thing also happens with DELETE:
psql 1:
teste=# create table teste (id integer primary key, parent integer references teste
(id));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'teste_pkey' for table
'teste'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
teste=# insert into teste values (1, null);
INSERT 826882 1
teste=# begin;
BEGIN
psql 2:
teste=# begin;
BEGIN
teste=# select 1 from teste where id = 1 for update;
?column?
----------
1
(1 row)
psql 1:
teste=# delete from teste where not exists (select 1 from teste where parent = 1);
[sits there waiting]
psql 2:
teste=# insert into teste values (2,1);
INSERT 826884 1
teste=# commit;
COMMIT
psql 1:
ERROR: <unnamed> referential integrity violation - key in teste still referenced from
teste
teste=# commit;
COMMIT
So, the problem is probably with the subselects. I wonder if UPDATE has
the same problem.
--
Cesar Eduardo Barros
ElNet Hightech -- Administrador de Sistemas Unix
[EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
