python - 引用复合索引作为另一个表的外键

Question

我在部门表中有一个复合索引，并且想要引用此列(唯一)作为员工表中的外键。问题是Flask migrate无法识别复合索引？

部门表:

class Department(db.Model):
    __tablename__ = "departments"

    id = db.Column(db.Integer, primary_key=True)
    dep_name = db.Column(db.String(250), nullable=False)

    employees = db.relationship('Employee',
                                   backref='department',
                                   lazy='dynamic')

    __table_args__ = (
        db.Index('dep_name_id', id, dep_name),
        db.UniqueConstraint(id, dep_name),
    )

员工表:

class Employee(db.Model):
    __tablename__ = "employees"

    someinfo = db.Column(db.String(ID_LEN), primary_key=True)
    department_id = db.Column(db.String(500), db.ForeignKey('departments.dep_name_id'))

但是运行flask db migrate后，出现以下错误(无法识别复合索引列!):

sqlalchemy.exc.NoReferencedColumnError: Could not initialize target column for ForeignKey 'deparments.dep_name_id' on table 'employees': 
table 'departments' has no column named 'dep_name_id'

Answer 1

首先将deb_name作为PK:

class Department(db.Model):
    ...
    dep_name = db.Column(db.String(250), nullable=False, primary_key=True)
    ...

之后，您可以在 Employee 模型上创建 department_name FK:

class Employee(db.Model):
    ...
    department_id = db.Column(db.String(500), db.ForeignKey('departments.dep_id'))
    department_name = db.Column(db.String(500), db.ForeignKey('departments.dep_name'))