黄兵的个人博客

SQLAlchemy 一个主键对应多个外键问题

今天在修改表结构的时候，一个主键对应多个外键，在新建的时候出现了如下错误：

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship PrivateNumberPropertyValue.user_shopping_carts - there are multiple foreign key paths linking the tables.  Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

出现错误的原因：

一个主键有多个外键链接到这张表上，要设置父表的外键引用。

解决方案：

设置foreign_keys外键引用：

# 购物车
class UserShoppingCart(db.Model):
    __tablename__ = 'user_shopping_carts'
    phone_number_section_id = db.Column(db.Integer,
                                        db.ForeignKey('private_number_property_value.id', ondelete='CASCADE'),
                                        index=True)
    phone_number_use_time_id = db.Column(db.Integer,
                                         db.ForeignKey('private_number_property_value.id', ondelete='CASCADE'),
                                         index=True)
    phone_number_amount_id = db.Column(db.Integer,
                                       db.ForeignKey('private_number_property_value.id', ondelete='CASCADE'),
                                       index=True)
    phone_number_section = db.relationship('PrivateNumberPropertyValue', foreign_keys=[phone_number_section_id],
                                           cascade='all, delete-orphan', passive_deletes=True)
    phone_number_use_time = db.relationship('PrivateNumberPropertyValue', foreign_keys=[phone_number_use_time_id],
                                            cascade='all, delete-orphan', passive_deletes=True)
    phone_number_amount = db.relationship('PrivateNumberPropertyValue', foreign_keys=[phone_number_amount_id],
                                          cascade='all, delete-orphan', passive_deletes=True)

但是如果这样设置，又会报错：

sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'mapped class UserShoppingCart->user_shopping_carts'. Original exception was: On UserShoppingCart.phone_number_amount, delete-orphan cascade is not supported on a many-to-many or many-to-one relationship when single_parent is not set.   Set single_parent=True on the relationship().

# 购物车
class UserShoppingCart(db.Model):
    __tablename__ = 'user_shopping_carts'
    phone_number_section_id = db.Column(db.Integer,
                                        db.ForeignKey('private_number_property_value.id', ondelete='CASCADE'),
                                        index=True)
    phone_number_use_time_id = db.Column(db.Integer,
                                         db.ForeignKey('private_number_property_value.id', ondelete='CASCADE'),
                                         index=True)
    phone_number_amount_id = db.Column(db.Integer,
                                       db.ForeignKey('private_number_property_value.id', ondelete='CASCADE'),
                                       index=True)
    phone_number_section = db.relationship('PrivateNumberPropertyValue', foreign_keys=[phone_number_section_id],
                                           cascade='all, delete-orphan', single_parent=True)
    phone_number_use_time = db.relationship('PrivateNumberPropertyValue', foreign_keys=[phone_number_use_time_id],
                                            cascade='all, delete-orphan', single_parent=True)
    phone_number_amount = db.relationship('PrivateNumberPropertyValue', foreign_keys=[phone_number_amount_id],
                                          cascade='all, delete-orphan', single_parent=True)