|InnoDB deviations from SQL standards:
1. INNODB accepts and ignores CHECK CONSTRAINT specifications, and FOREIGN KEY references to tables that do not support them.
2. INNODB permits non-unique foreign keys. If the parent key has duplicates, INNODB permits deletion of none of them if there is a matching child row.
3. Some errors do not rollback the entire transaction they occur in:
drop table if exists t; create table t(i int primary key, j int not null) engine=innodb; start transaction; insert into t values(1,1),(2,2); update t set j=j+1; insert into t values(2,null); commit; select * from t; +---+---+ | i | j | +---+---+ | 1 | 2 | | 2 | 3 | +---+---+Workaround: use an ERROR HANDLER ...
drop table if exists t; create table t(i int primary key, j int not null) engine=innodb; delimiter go create procedure t() begin start transaction; BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND ROLLBACK; insert into t values(1,1),(2,2); update t set j=j+1; insert into t values(3,null); END; commit; select * from t; end ; go delimiter ; call t();
4. Self-referential ON UPDATE CASCADE and ON UPDATE SET NULL are not supported; recursion on them acts like RESTRICT. CASCADE may be nested only to 15 levels.
5. UNIQUE and FOREIGN KEY constraints checks are row-by-row, and cannot be deferred until statement end, so a row that refers to itself via a foreign key cannot be deleted.
6. A foreign key action updating or deleting a referencing table does not fire Triggers on the affected table (http://bugs.mysql.com/bug.php?id=11472). Also CASCADE foreign key updates do not update DATETIME and TIMESTAMP columns with ON UPDATE clauses.
7. After a server restart, the INNODB auto_increment counter resets to the next available value, so higher deleted key values will be re-used.
8. An accurate SELECT COUNT(*) result depends on isolation level, pending transactions, and what's in buffers and logs. If innodb_stats_on_metadata is set, INNODB updates statistics when SHOW TABLE STATUS or SHOW INDEX runs.
Last updated 16 Aug 2019