[MDEV-9008] With foreign_key_checks=0 I can DROP a table, but cannot convert it to MyISAM Created: 2015-10-25  Updated: 2015-10-25  Resolved: 2015-10-25

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.1.8
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Federico Razzoli Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: foreign-keys


 Description    

CREATE OR REPLACE TABLE t1
 
(
 
	a INT PRIMARY KEY,
 
	b INT
 
)
 
	ENGINE = InnoDB
 
;
 
 
 
CREATE OR REPLACE TABLE t2
 
(
 
	a INT PRIMARY KEY,
 
	b INT,
 
	FOREIGN KEY (b) REFERENCES t1 (a)
 
)
 
	ENGINE = InnoDB
 
;
 
 
 
MariaDB [test]> SET foreign_key_checks = 0;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> ALTER TABLE t2 ENGINE = MyISAM;
 
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
 
MariaDB [test]> DROP TABLE t2;
 
Query OK, 0 rows affected (0.00 sec)



 Comments   
Comment by Elena Stepanova [ 2015-10-25 ]

This is a documented exception for foreign_key_checks, see http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html :

However, even if foreign_key_checks = 0, <...> if a table has foreign key constraints, ALTER TABLE cannot be used to alter the table to use another storage engine

Generated at Thu Feb 08 07:31:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.