Не выходит применить миграции
sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1215, 'Cannot add foreign key constraint') [SQL: 'ALTER TABLE abbrev_food_group ADD FOREIGN KEY(ndb) REFERENCES abbrev (ndb)']
Был разный тип данных в ndb, привел к Integer, ошибка осталась, как исправить?
orm
__tablename__ = 'abbrev'
ndb = db.Column(db.Integer, primary_key=True)
Description = db.Column(db.String(300))
food_group = db.relationship('USDAIngredientFoodGroup', backref='foodGroup', lazy='dynamic')
Water = db.Column(db.Float(10))
Energ_Kcal = db.Column(db.Float(10))
Protein = db.Column(db.Float(10))
Lipid_Tot = db.Column(db.Float(10))
Ash = db.Column(db.Float(10))
Carbohydrt = db.Column(db.Float(10))
Fiber_TD = db.Column(db.Float(10))
Sugar_Tot = db.Column(db.Float(10))
Calcium = db.Column(db.Float(10))
Iron = db.Column(db.Float(10))
Magnesium = db.Column(db.Float(10))
Phosphorus = db.Column(db.Float(10))
Potassium = db.Column(db.Float(10))
Sodium = db.Column(db.Float(10))
Zinc = db.Column(db.Float(10))
Copper = db.Column(db.Float(10))
Manganese = db.Column(db.Float(10))
Selenium = db.Column(db.Float(10))
Vit_C = db.Column(db.Float(10))
Thiamin = db.Column(db.Float(10))
Riboflavin = db.Column(db.Float(10))
Niacin = db.Column(db.Float(10))
Panto_acid = db.Column(db.Float(10))
Vit_B6 = db.Column(db.Float(10))
Folate_Tot = db.Column(db.Float(10))
Folic_acid = db.Column(db.Float(10))
Food_Folate = db.Column(db.Float(10))
Folate_DFE = db.Column(db.Float(10))
Choline_Tot = db.Column(db.Float(10))
Vit_B12 = db.Column(db.Float(10))
Vit_A_IU = db.Column(db.Float(10))
Vit_A_RAE = db.Column(db.Float(10))
Retinol = db.Column(db.Float(10))
Alpha_Carot = db.Column(db.Float(10))
Beta_Carot = db.Column(db.Float(10))
Beta_Crypt = db.Column(db.Float(10))
Lycopene = db.Column(db.Float(10))
Lut_Zea = db.Column('Lut+Zea', db.Float(10))
Vit_E = db.Column(db.Float(10))
Vit_D_mcg = db.Column(db.Float(10))
Vit_D_IU = db.Column(db.Float(10))
Vit_K = db.Column(db.Float(10))
FA_Sat = db.Column(db.Float(10))
FA_Mono = db.Column(db.Float(10))
FA_Poly = db.Column(db.Float(10))
Cholestrl = db.Column(db.Float(10))
Refuse_Pct = db.Column(db.Float(10))
GmWt_1 = db.Column(db.Float(10))
GmWt_2 = db.Column(db.Float(10))
GmWt_Desc1 = db.Column(db.String(77))
GmWt_Desc2 = db.Column(db.String(80))
Shrt_Desc = db.Column(db.String(60))
__tablename__ = 'abbrev_food_group'
ndb = db.Column(db.Integer, db.ForeignKey('abbrev.ndb'), primary_key=True)
group_id = db.Column(db.Integer, db.ForeignKey('food_group.group_id'))
что в самой базе творится
abbrev | CREATE TABLE `abbrev` (
`ndb` int(11) NOT NULL,
`Shrt_Desc` varchar(60) DEFAULT NULL,
`Water` decimal(10,0) DEFAULT NULL,
`Energ_Kcal` decimal(10,0) DEFAULT NULL,
`Protein` decimal(10,0) DEFAULT NULL,
`Lipid_Tot` decimal(10,0) DEFAULT NULL,
`Ash` decimal(10,0) DEFAULT NULL,
`Carbohydrt` decimal(10,0) DEFAULT NULL,
`Fiber_TD` decimal(10,0) DEFAULT NULL,
`Sugar_Tot` decimal(10,0) DEFAULT NULL,
`Calcium` decimal(10,0) DEFAULT NULL,
`Iron` decimal(10,0) DEFAULT NULL,
`Magnesium` decimal(10,0) DEFAULT NULL,
`Phosphorus` decimal(10,0) DEFAULT NULL,
`Potassium` decimal(10,0) DEFAULT NULL,
`Sodium` decimal(10,0) DEFAULT NULL,
`Zinc` decimal(10,0) DEFAULT NULL,
`Copper` decimal(10,0) DEFAULT NULL,
`Manganese` decimal(10,0) DEFAULT NULL,
`Selenium` decimal(10,0) DEFAULT NULL,
`Vit_C` decimal(10,0) DEFAULT NULL,
`Thiamin` decimal(10,0) DEFAULT NULL,
`Riboflavin` decimal(10,0) DEFAULT NULL,
`Niacin` decimal(10,0) DEFAULT NULL,
`Panto_acid` decimal(10,0) DEFAULT NULL,
`Vit_B6` decimal(10,0) DEFAULT NULL,
`Folate_Tot` decimal(10,0) DEFAULT NULL,
`Folic_acid` decimal(10,0) DEFAULT NULL,
`Food_Folate` decimal(10,0) DEFAULT NULL,
`Folate_DFE` decimal(10,0) DEFAULT NULL,
`Choline_Tot` decimal(10,0) DEFAULT NULL,
`Vit_B12` decimal(10,0) DEFAULT NULL,
`Vit_A_IU` decimal(10,0) DEFAULT NULL,
`Vit_A_RAE` decimal(10,0) DEFAULT NULL,
`Retinol` decimal(10,0) DEFAULT NULL,
`Alpha_Carot` decimal(10,0) DEFAULT NULL,
`Beta_Carot` decimal(10,0) DEFAULT NULL,
`Beta_Crypt` decimal(10,0) DEFAULT NULL,
`Lycopene` decimal(10,0) DEFAULT NULL,
`Lut+Zea` decimal(10,0) DEFAULT NULL,
`Vit_E` decimal(10,0) DEFAULT NULL,
`Vit_D_mcg` decimal(10,0) DEFAULT NULL,
`Vit_D_IU` decimal(10,0) DEFAULT NULL,
`Vit_K` decimal(10,0) DEFAULT NULL,
`FA_Sat` decimal(10,0) DEFAULT NULL,
`FA_Mono` decimal(10,0) DEFAULT NULL,
`FA_Poly` decimal(10,0) DEFAULT NULL,
`Cholestrl` decimal(10,0) DEFAULT NULL,
`GmWt_1` decimal(10,0) DEFAULT NULL,
`GmWt_Desc1` varchar(77) DEFAULT NULL,
`GmWt_2` decimal(10,0) DEFAULT NULL,
`GmWt_Desc2` varchar(80) DEFAULT NULL,
`Refuse_Pct` decimal(10,0) DEFAULT NULL,
`Description` varchar(300) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
abbrev_food_group | CREATE TABLE `abbrev_food_group` (
`ndb` int(11) NOT NULL,
`group_id` int(11) DEFAULT NULL,
PRIMARY KEY (`ndb`),
KEY `group_id` (`group_id`),
CONSTRAINT `abbrev_food_group_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `food_group` (`group_id`),
CONSTRAINT `abbrev_food_group_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `food_group` (`group_id`),
CONSTRAINT `abbrev_food_group_ibfk_3` FOREIGN KEY (`group_id`) REFERENCES `food_group` (`group_id`),
CONSTRAINT `abbrev_food_group_ibfk_4` FOREIGN KEY (`group_id`) REFERENCES `food_group` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC |
Прописал show engine innodb status
LATEST FOREIGN KEY ERROR
------------------------
2017-07-05 18:17:20 0x7fa5c8083700 Error in foreign key constraint of table kitchry/#sql-3f4_89:
FOREIGN KEY(ndb) REFERENCES abbrev (ndb):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.
Такой таблицы нет в бд #sql-3f4_89
