Hi Steffen,

> > > Then again, Uniques might be useful deferred as well. For example,
> > > one cannot do this:
> > >
> > > CREATE TABLE mytable
> > > (
> > > INTCOL INTEGER NOT NULL PRIMARY KEY
> > > )
> > > COMMIT;
> > > INSERT INTO mytable VALUES (1);
> > > INSERT INTO mytable VALUES (2);
> > > INSERT INTO mytable VALUES (3);
> > > COMMIT;
> > >
> > > UPDATE mytable SET intcol = intcol + 1;
> > >
> > > While the constraint itself - after all rows have been updated - is
> > > perfectly valid.
>
> No. This *SHOULD* not be perfectly valid. Let me extend your example:
>
> CREATE TABLE mytable
> (
> INTCOL INTEGER NOT NULL PRIMARY KEY
> INTCOLO INTEGER NOT NULL
> )
> COMMIT;
> INSERT INTO mytable VALUES (1,1);
> INSERT INTO mytable VALUES (2,2);
> INSERT INTO mytable VALUES (3,3);
> COMMIT;
> UPDATE mytable SET intcol = 2 where intcolo = 1;
> UPDATE mytable SET intcol = 3 where intcolo = 2;
> UPDATE mytable SET intcol = 4 where intcolo = 3;
> UPDATE mytable SET intcol0 = intcol;
> COMMIT;
>
> This should be exactly your example, right?

No, it isn't :-)

> [I expanded the UPDATE-Statement, since I need this for the next part. But
> so far, this should not have changed anything!]

MS SQL fails on the UPDATE statement:
Violation of PRIMARY KEY constraint 'PK__mytable__56B3DD81'. Cannot insert
duplicate key in object 'mytable'

> Now consider, there would have been the following table:
>
> CREATE TABLE mydetails
> (
> INTCOL INTEGER NOT NULL
> )
> COMMIT;
> ALTER TABLE mydetails ADD CONSTRAINT xxx FOREIGN KEY (INTCOL) REFERENCES
> mytable (INTCOL) ON DELETE CASCADE ON UPDATE CASCADE;
> COMMIT;
> INSERT INTO mydetails VALUES (1);
> INSERT INTO mydetails VALUES (2);
> INSERT INTO mydetails VALUES (3);
> COMMIT;
>
> Now, what should happen upon the execution of the second UPDATE command?
> And remember, the same problem occurs, if there would be only the original
> UPDATE statment, it would only be a little harder to explain.
>
> I want to show with this example, that uniqueness MUST prevail between
> updates and MAY NOT be deferred.
>
> > btw, MySQL/InnoDB fails on this as well.
>
> Fine.
>
> > MS SQL Server does allow this.
>
> Oh my good. I love microsoft software (for real, don't kill me), but how
> would they try to handle the above?

See above. There IS a difference - it's not a deferred constraint in MS SQL
either. However, the statement is atomic (update pk = pk + 1) and can be
executed.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com