1. Before MySQL version 8.0, INNODB accepts and ignores CHECK CONSTRAINT specifications, and FOREIGN KEY references to tables that do not support FOREIGN KEY declarations.

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 |
+---+---+ 

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 7 May 2024