python - 在 SQLAlchemy 中创建外键失败

Question

我希望每个用户都有一个名为 allocatedstorage 和 availablestorage 的值。这两个值存储在另一个表中。但是，当我创建以下数据库时，它显示以下错误。我究竟做错了什么？感谢您的帮助。

class User(UserMixin, db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(15), unique=True)
    email = db.Column(db.String(50), unique=True)
    password = db.Column(db.String(80))
    role= db.Column(db.String(20));
    usersessiontoken = db.Column(db.String(500), unique=True)
    hasstorage = db.Column(db.Integer, db.ForeignKey('storageinformation.allocatedstorage'))
    hasavailablestorage = db.Column(db.Integer, db.ForeignKey('storageinformation.availablestorage'))

    storageinformation = db.relationship('StorageInformation', backref='user')

class StorageInformation(UserMixin, db.Model):
    __tablename__ = 'storageinformation'
    id = db.Column(db.Integer, primary_key=True)
    allocatedstorage = db.Column(db.Integer)
    availablestorage = db.Column(db.Integer)

错误:

sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1215, 'Cannot add foreign key constraint') [SQL: '\nCREATE TABLE user (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tusername VARCHAR(15), \n\temail VARCHAR(50), \n\tpassword VARCHAR(80), \n\t`role` VARCHAR(20), \n\tusersessiontoken VARCHAR(500), \n\thasstorage INTEGER, \n\thasavailablestorage INTEGER, \n\tPRIMARY KEY (id), \n\tUNIQUE (username), \n\tUNIQUE (email), \n\tUNIQUE (usersessiontoken), \n\tFOREIGN KEY(hasstorage) REFERENCES storageinformation (allocatedstorage), \n\tFOREIGN KEY(hasavailablestorage) REFERENCES storageinformation (availablestorage)\n)\n\n']

Answer 1

• 就我而言，迁移命令的输出中并未显示实际的原因
• 我只能通过在数据库控制台中运行相同的命令来发现问题

命令是
更改表分配添加外键(session_id)引用电池(session_id)

实际问题
使用外键约束创建表“db_name/#sql-5f9_11e”失败。引用的表中没有索引，其中引用的列显示为第一列。
无法添加外键约束


Mysql Doc -> Using FOREIGN KEY Constraints

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order.

InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.