![]() Indeed, I found this answer from which describes a way that might probably solve this and indeed it seemed to work. In the end, I was forced to dump the whole schema and restore it with another name, then drop the schema with the old name (and containing the problematic/corrupted SOURCE_TABLE). Is there anything else I can try which does not involve dropping this schema? It's quite huge and the downtime would be relevant. The suggested workaround is to drop the schema and re-create from a dump file. Looking at this and this, I suspect that something occurred in the past that caused that foreign key to be orphaned: indeed, this SOURCE_TABLE was renamed some time ago and I'm pretty sure the foreign key I'm trying to add was there in the past. Returns all the foreign key constraints in my_schema that I can also get from SHOW CREATE TABLE statements, but no sign of ConstraintFK. Returns an empty set, while: SELECT * FROM information_schema.TABLE_CONSTRAINTSĪND information_schema.TABLE_CONSTRAINTS.CONSTRAINT_SCHEMA = 'my_schema' ![]() WHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY'ĪND information_schema.TABLE_CONSTRAINTS.CONSTRAINT_NAME = 'ConstraintFK' Unfortunately, trying to query the information schema does not help: SELECT * FROM information_schema.TABLE_CONSTRAINTS I looked at the file system and I see no reasons for which renaming of the table should fail. Error on rename of './my_schema/SOURCE_TABLE' to './my_schema/#sql2-4c0c-b6fc8ca' (errno: 152) Indeed, if I try to drop that constraint: ALTER TABLE `SOURCE_TABLE` ) ENGINE=InnoDB AUTO_INCREMENT=58108 DEFAULT CHARSET=utf8 ) ENGINE=InnoDB AUTO_INCREMENT=4089 DEFAULT CHARSET=utf8 The latter shows that the index for the foreign key exists, but the foreign key constraint seems not to be there: - only relevant info shown ![]() Of course, there's no constraint named ConstraintFK defined in this schema, I checked both the information schema and the SHOW CREATE TABLE SOURCE_TABLE output. The character case, then collisions in constraint If youĬreate tables or databases whose names differ only in MySQL standard latin1_swedish_ci collation. Note that InnoDB's FOREIGN KEY system tables storeĬonstraint names as case-insensitive, with the In front of the user-defined constraint name.) (Note that internally InnoDB adds 'databasename' Looking at SHOW ENGINE INNODB STATUS I get: -Ģ20523 16:34:36 Error in foreign key constraint creation for table `my_schema`.`#sql-4c0c_b6fc8ca`.Ī foreign key constraint of name `my_schema`.`ConstraintFK`Īlready exists. Can't create table 'my_schema.#sql-4c0c_b6fc8ca' (errno: 121) MySQL returns the following error: Error Code: 1005. I have a table for which I can't add a foreign key: ALTER TABLE `SOURCE_TABLE` You are runnning the last release of DaDaBIKĬlient: Mozilla/5.0 (Windows NT 6.1 Win64 圆4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/.102 Safari/537.Using MySQL 5.5. ![]() You are using DaDaBIK version 9.2-Monterosso enterprise, installed on 11-15-2018 (installation code: 140855bc739c14ccd4), the latest version of DaDaBIK is 9.2-Monterosso released on 11-07-2018 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 KEY `fk_expences_debt_cancell_idx` (`debt_cancell_idpersonal_details`),ĬONSTRAINT `fk_expences_debt_cancell` FOREIGN KEY (`debt_cancell_idpersonal_details`) REFERENCES `debt_cancell` (`idpersonal_details`) ON DELETE CASCADE ON UPDATE CASCADE UNIQUE KEY `idexpences_UNIQUE` (`idexpences`), `debt_cancell_idpersonal_details` int(10) unsigned NOT NULL, `expences_expense` varchar(45) DEFAULT NULL, `idexpences` int(11) NOT NULL AUTO_INCREMENT,
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |