> > create table t1
> > (
> >         f1 integer,
> >         f2 integer
> > );
> > 
> > create table t2
> > (
> >         f1 integer references t1(f1),
> >         f2 integer
> > );
> 
> > begin transaction;
> > insert into t1(f1,f2) values(1,1);
> > delete from t1 where f1=1;
> 
> > ERROR:  triggered data change violation on relation "t1"
> 
> You cannot change data twice within a transaction if there's a RI
> constraint on the table.  This is per SQL, nothing we can do about it.
                            ^^^^^^^^^^^^^^^
Is it true?! *Any reasons* for this? DELETE doesn't break integrity rules.
Just tested it in Oracle - deletion is allowed!
But yes, I know that Oracle doesn't always follow standards -:)
Can someone test it under Informix, others?

Vadim

Tue, 10 Oct 2000 13:07:13 -0700
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM>
RE: POSTGRES BUG - FIX IT PLEASE
Mikheev, Vadim writes:

> > You cannot change data twice within a transaction if there's a RI
> > constraint on the table.  This is per SQL, nothing we can do about it.
>                             ^^^^^^^^^^^^^^^
> Is it true?!

Actually, it's not.  Jan Wieck first explained this on July 23 in "Re:
[GENERAL] failed Delete after Insert in a transaction", and we've been
believing it ever since, but I just found out that it's wrong.

The standard reads

         11.8 <referential constraint definition>

         9) If any attempt is made within an SQL-statement to update some
            site to a value that is distinct from the value to which that
            site was previously updated within the same SQL-statement,
            then an exception condition is raised: triggered data change
            violation.
 
        10) If a site in an object row is an <object column> of an <update
            statement: positioned> or <update statement: searched>, and
            there is any attempt within the same SQL-statement to delete the
            row containing that site, then an exception condition is raised:
            triggered data change violation.                                                              

(also  11.8 GR 8 b) i) 2), but it's too boring to quote...)

Note that it talks about "statements", not "transactions".

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


Tue, 10 Oct 2000 23:05:23 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: [HACKERS] RE: POSTGRES BUG - FIX IT PLEASE