error 1005, "Can't create table - SQLAlchemy to MySQL

Question

Trying to connect to MySQL from Flask-SQLAlchemy but receive

sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1005, "Can't create table ql12255647.member_courses... when typed command db.create_all()

When i use sqlite:////tmp/test.db everything is fine and no errors

Need to solve problem. My code is below:

member_courses = db.Table('member_courses',
      db.Column ('member_id', db.String (80), db.ForeignKey('member.id')),
      db.Column ('course_id', db.String (80), db.ForeignKey('course.id')))

class Course(db.Model):
     id = db.Column(db.Integer, primary_key=True)
     course = db.Column(db.String(80), unique = True)

class Member(db.Model):
     id = db.Column(db.Integer,primary_key=True)
     name = db.Column(db.String(80), unique = True)
     email = db.Column(db.String(80), unique = True)
     courses = db.relationship('Course', secondary='member_courses', 
     backref='member', 
     lazy = 'dynamic')

Error:

sqlalchemy.exc.InternalError: (pymysql.err.InternalError) 
(1005, "Can't create table 'sql12255647.member_courses' (errno: 150)") 
[SQL: '\nCREATE TABLE member_courses (\n\tmember_id VARCHAR(80), \n\tcourse_id VARCHAR(80), \n\tFOREIGN KEY(member_id) REFERENCES member (id), \n\tFOREIGN KEY(course_id) REFERENCES course (id)\n)\n\n']
(Background on this error at: http://sqlalche.me/e/2j85)

The referencing columns have type VARCHAR(80), while the referenced columns have INTEGER. You don't get errors in SQLite because it has a very different type system from most other SQL DBMS, and it doesn't even enforce foreign key integrity by default (it has to be enabled). — Ilja Everilä, Sep 14 '18 at 09:22

score 2 · Answer 1 · answered Sep 12 '18 at 14:12

Error Code 1005 refers to table creation failure because a Foreign Key constraint was not correctly formed. To solve this error, you likely just have to ensure that your foreign key has the exact same type as the Primary key.

The accepted answer here provides a detailed overview of scenarios that you can verify.

Hope this helps.

error 1005, "Can't create table - SQLAlchemy to MySQL

1 Answers1