WL#2235: SQL standard conformant checking of CONSTRAINTs and FOREIGN KEYs
Affects: Server-7.1 — Status: Un-Assigned — Priority: Medium
According to the SQL standard CONSTRAINTs like UNIQUE or FOREIGN KEYs should be checked, when the WHOLE SQL statement has been processed. Currently MySQL(InnoDB FOREIGN KEYs) checks UNIQUE and FOREIGN KEY constraints row-by-row during the processing of the statement. That behaviour differs from the majority of SQL databases and decreases the usability of these constraints in production environments where SQL statements like INSERT, UPDATE and DELETE with intermediate (during statement processing) violations of these constraints occur. The implementation of this feature would also solve many problems within the NIST tests. WL#148 "Foreign keys: Implement Foreign Keys (all storage engines)" currently says that end-of-statement checks will occur, when the storage engine is capable, for foreign keys. Feature requests: BUG#5573 Wrong/undocumented behaviour on UNIQUE column mass update BUG#7290 update should check primary keys after all records altered BUG#35856 false unique constraint violation on multiple updates
Copyright (c) 2000, 2018, Oracle Corporation and/or its affiliates. All rights reserved.