Flask-Migration with one-to-many relationship

Question

I have a working blog system. I want to add it to comment system. I completed migrations with post model with id, title and body.

Now I add comments and create new model named Comment. When I run migrations:

INFO [alembic.runtime.migration] Context impl MySQLImpl.

INFO [alembic.runtime.migration] Will assume non-transactional DDL.

INFO [alembic.env] No changes in schema detected.

from run import db

class Post(db.Model):
    __tablename__ = 'blog.post'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String, nullable=False)
    body = db.Column(db.Text, nullable=False)

    comments = db.relationship('Comment', backref='blog.post')


from run import db


class Comment(db.Model):   
    __tablename__ = 'blog.comment'
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.Text, nullable=False)

    post_id = db.Column(db.Integer, db.ForeignKey('blog.post.id'), nullable=False)

I dont know what is wrong with my code. I get relationship from documentation and edit it. There aren't any comment table in db before.

EDIT 1: I call comment inside run like below: from model.comment import Comment

After that I can create migration but migration got error like below:

sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1005, 'Can\'t create table blog_db.blog.comment (errno: 150 "Foreign key constraint is incorrectly formed")') [SQL: '\nCREATE TABLE blog.comment (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tname VARCHAR(255) NOT NULL, \n\tbody TEXT NOT NULL, \n\tcreated DATETIME DEFAULT now(), \n\tstatus INTEGER NOT NULL, \n\tpost_id INTEGER NOT NULL, \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(post_id) REFERENCES blog.post (id)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/2j85)

Is that code in 2 separate modules? Are they both being imported? Are you manually calling db.create_all()? — gtalarico, Feb 18 '18 at 08:18
They in the same module. I do not call db.create_all() anywhere. I edit my topic — guarillamail, Feb 18 '18 at 10:20

score 1 · Answer 1 · answered Feb 19 '18 at 06:49

The error Foreign key constraint is incorrectly formed happens because the primary key and the foreign key have different types.

In the Post model you defined the id column as:

id = db.Column(db.Integer, primary_key=True)

But the post_id foreign key that you added in the Comment model is defined differently:

post_id = db.Column(db.Integer, db.ForeignKey('blog.post.id'), nullable=False)

I think if you remove the nullable clause from the foreign key you'll get the migration accepted.

Flask-Migration with one-to-many relationship

1 Answers1