Hi

> > 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?
[I expanded the UPDATE-Statement, since I need this for the next part. But
so far, this should not have changed anything!]

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?


Regards,
Steffen