Creating a foreign key in SQLAlchemy fails

Question

I want every user to have a value called allocatedstorage and availablestorage. Those two values are stored in another table. However when I create the following database it shows the error below. What am I doing wrong? Thanks for your help.

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)

Error:

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']

You've 2 columns trying to reference a foreign **key** column, but neither referenced columns are keys. Did you mean to add a single foreign key column such as `storageinformation_id = db.Column(db.Integer, db.ForeignKey('storageinformation.id'))`? — Ilja Everilä, Sep 25 '17 at 17:58
Possible duplicate of [MySQL Error 1215: Cannot add foreign key constraint](https://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint) — Ilja Everilä, Sep 25 '17 at 18:02

score 1 · Answer 1 · answered Oct 05 '18 at 21:25

In my case, the actual reason was not shown in the output of migration command
I was able found the problem only by running the same command in database console

Command was ALTER TABLE assignment ADD FOREIGN KEY(session_id) REFERENCES battery (session_id)

Actual problem Create table 'db_name/#sql-5f9_11e' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns. Cannot add foreign key constraint

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.

Creating a foreign key in SQLAlchemy fails

1 Answers1