Statement-Level Late Check of Constraint and SQL Standard

Test case:

create table t (x int primary key);
insert into t values (1);
insert into t values (2);
update t set x = x + 1;

In Oracle, the update works, without a violation of the primary key constraint:

SQL> create table t (x int primary key);

Table created.

SQL> insert into t values (1);

1 row created.

SQL> insert into t values (2);

1 row created.

SQL> update t set x = x + 1;

2 rows updated.

Oracle, SQL Server, DB2, and MySQL with the old MyISAM storage engine behave the same: the last update statement successfully updates both rows, to 2 and 3, respectively.

MySQL with InnoDB engine (which is default in modern versions) fails with "ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'". Neither row is updated.

Microsoft Access throws an error but gives an option to continue with the rest of the rows (which updates 2 to 3 and leaves 1 alone), or to abort (which leaves both rows alone).

In case of MySQL, the InnoDB documentation states that "InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row... According to the SQL standard, the default behavior should be deferred checking... Until InnoDB implements deferred constraint checking...", implying that the statement- (not transaction-) level late or deferred constraint check is not yet but will be implemented. (Note that this statement-level late constraint check has nothing to do with the concept of Oracle deferred constraint, which is at the transaction level. To avoid confusion, it's called "late" instead of "deferred" in this note.)

The "Constraint checking" section of the 2011 4th ed. SQL Standard (Part 1) states that "On completion of execution of every SQL-statement, every enforced constraint is checked whose constraint mode is immediate" (my italic). There's no mention of constraint check at other times, such as at the beginning of the SQL execution (in anticipation of a violation), or during the SQL execution (in a row-by-row mode). Therefore, the implementation of Oracle, SQL Server, and DB2 are compliant with the standard, while MySQL with current InnoDB engine and Access are not.

According to Chris J. Date, SQL Standard defines NO ACTION and later RESTRICT options to handle a constraint. NO ACTION, the default, checks the constraint violation at the completion of the SQL statement, while RESTRICT checks at the beginning.^note1 Apparently, some databases such as DB2 have implemented them in ON DELETE action of a foreign key. Although MySQL allows the RESTRICT keyword in ON DELETE and ON UPDATE clauses, it's treated as synonymous with ACTION.

______________________________

[note1] 4th ed. SQL Standard (Part 2), p.77, "NOTE 53 -- Even if constraint checking is not deferred, ON UPDATE RESTRICT is a stricter condition than ON UPDATE NO ACTION. ON UPDATE RESTRICT prohibits an update to a particular row if there are any matching rows; ON UPDATE NO ACTION does not perform its constraint check until the entire set of rows to be updated has been processed." And on p.78, "NOTE 54 -- Even if constraint checking is not deferred, ON DELETE RESTRICT is a stricter condition than ON DELETE NO ACTION. ON DELETE RESTRICT prohibits a delete of a particular row if there are any matching rows; ON DELETE NO ACTION does not perform its constraint check until the entire set of rows to be deleted has been processed."

To my OraNotes Page