黄兵的个人博客

Foreign key associated with column 'material_product_pro.pp_fk_pn' could not find table 'material_pro_name' with which to generate a foreign key to target column 'pro_id'

在使用SQLAlchemy更新数据库的时候，出现如下错误提示：

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'material_product_pro.pp_fk_pn' could not find table 'material_pro_name' with which to generate a foreign key to target column 'pro_id'

# 产品属性
class MaterialProductProperty(db.Model):
    __tablename_ = 'material_product_pro'
    pp_id = db.Column(db.Integer, primary_key=True)
    pp_fk_p = db.Column(db.Integer, db.ForeignKey('material_product.p_id'), index=True)
    pp_fk_pv = db.Column(db.Integer, db.ForeignKey('material_product_value.pv_id'), index=True)
    pp_fk_pn = db.Column(db.Integer, db.ForeignKey('material_pro_name.pro_id'), index=True)


# 产品sku
class MaterialProductSKU(db.Model):
    __tablename_ = 'material_product_sku'
    ps_id = db.Column(db.Integer, primary_key=True)
    pd_fk_id = db.Column(db.Integer, db.ForeignKey('material_product.p_id'), default=None, index=True)
    pd_num = db.Column(db.Integer, default=None)
    pd_price = db.Column(db.DECIMAL(10, 4), default=None)
    pd_name = db.Column(db.VARCHAR(50), default=None)
    pd_properties = db.Column(db.VARCHAR(300), default=None)


# 产品名称
class MaterialProductName(db.Model):
    __tablename_ = 'material_pro_name'
    pro_id = db.Column(db.Integer, primary_key=True)
    pro_name = db.Column(db.VARCHAR(50))
    pro_fk_id = db.Column(db.Integer, db.ForeignKey('material_item.i_id'), index=True)
    pro_has_otherName = db.Column(db.CHAR(2), default=0)
    pro_has_color = db.Column(db.CHAR(2), default=0)
    pro_has_enum = db.Column(db.CHAR(2), default=0)
    pro_has_input = db.Column(db.CHAR(2), default=0)
    pro_is_key = db.Column(db.CHAR(2), default=0)
    pro_is_sale = db.Column(db.CHAR(2), default=0)
    pro_is_must = db.Column(db.CHAR(2), default=0)
    pn_rel_pv = db.relationship('MaterialProductValue', backref='material_pro_name')
    pn_rel_pp = db.relationship('MaterialProductProperty', backref='material_pro_name')


# 材料值表
class MaterialProductValue(db.Model):
    __tablename__ = 'material_product_value'
    pv_id = db.Column(db.Integer, primary_key=True)
    pv_name = db.Column(db.VARCHAR(50))
    pv_fk_pid = db.Column(db.Integer, db.ForeignKey('material_pro_name.pro_id'), index=True)
    pv_rel_pp = db.relationship('MaterialProductProperty', backref='material_product_value')

第一眼看去也没有什么问题，但是却报这样的错误。

我在github找了一下答案：

My recommendation is that you specify your own table names using __tablename__, that way they are explicit in the code and not magically determined for you.

大意是说要加__tablename__但是我上面已经加了，顺便给了代码如下：

class Job(db.Model):
    __tablename__ = 'jobs'
    id = db.Column(db.Integer, primary_key=True)
    list_type_id = db.Column(db.Integer, db.ForeignKey('list_types.id'),
                             nullable=False)
    # ...

class ListType(db.Model):
    __tablename__ = 'list_types'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), unique=True, nullable=False)
    # ...

解决方案：

之后我对照我的代码，原来我写的__tablename_少了一个下划线，但是PyCharm也没有提示，修改之后代码如下：

# 产品属性
class MaterialProductProperty(db.Model):
    __tablename__ = 'material_product_pro'
    pp_id = db.Column(db.Integer, primary_key=True)
    pp_fk_p = db.Column(db.Integer, db.ForeignKey('material_product.p_id'), index=True)
    pp_fk_pv = db.Column(db.Integer, db.ForeignKey('material_product_value.pv_id'), index=True)
    pp_fk_pn = db.Column(db.Integer, db.ForeignKey('material_pro_name.pro_id'), index=True)


# 产品sku
class MaterialProductSKU(db.Model):
    __tablename__ = 'material_product_sku'
    ps_id = db.Column(db.Integer, primary_key=True)
    pd_fk_id = db.Column(db.Integer, db.ForeignKey('material_product.p_id'), default=None, index=True)
    pd_num = db.Column(db.Integer, default=None)
    pd_price = db.Column(db.DECIMAL(10, 4), default=None)
    pd_name = db.Column(db.VARCHAR(50), default=None)
    pd_properties = db.Column(db.VARCHAR(300), default=None)


# 产品名称
class MaterialProductName(db.Model):
    __tablename__ = 'material_pro_name'
    pro_id = db.Column(db.Integer, primary_key=True)
    pro_name = db.Column(db.VARCHAR(50))
    pro_fk_id = db.Column(db.Integer, db.ForeignKey('material_item.i_id'), index=True)
    pro_has_otherName = db.Column(db.CHAR(2), default=0)
    pro_has_color = db.Column(db.CHAR(2), default=0)
    pro_has_enum = db.Column(db.CHAR(2), default=0)
    pro_has_input = db.Column(db.CHAR(2), default=0)
    pro_is_key = db.Column(db.CHAR(2), default=0)
    pro_is_sale = db.Column(db.CHAR(2), default=0)
    pro_is_must = db.Column(db.CHAR(2), default=0)
    pn_rel_pv = db.relationship('MaterialProductValue', backref='material_pro_name')
    pn_rel_pp = db.relationship('MaterialProductProperty', backref='material_pro_name')


# 材料值表
class MaterialProductValue(db.Model):
    __tablename__ = 'material_product_value'
    pv_id = db.Column(db.Integer, primary_key=True)
    pv_name = db.Column(db.VARCHAR(50))
    pv_fk_pid = db.Column(db.Integer, db.ForeignKey('material_pro_name.pro_id'), index=True)
    pv_rel_pp = db.relationship('MaterialProductProperty', backref='material_product_value')

修改之后问题解决。

参考资料：

Flask-Migrate sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column

黄兵个人博客原创。

转载请注明出处：黄兵个人博客 - Foreign key associated with column 'material_product_pro.pp_fk_pn' could not find table 'material_pro_name' with which to generate a foreign key to target column 'pro_id'