3

I am using SQLAlchemy with Flask to create database tables - every table has at least one foreign key - it works with sqlite but not MySQL - I get foreign key integrity error when creating the tables in MySQL (the parent table is not created when creating the child table). I use "SET foreign_key_checks = 0" to solve the problem but that does not work with sqlite. Is there a way to configure SQLAlchemy to ignore foreign key checks?

dami.max
  • 377
  • 3
  • 17
  • sqlite does not enforce foreign keys by default. – univerio Apr 15 '16 at 20:35
  • 1
    It sounds like what you are actually asking *Is there a way to configure SQLAlchemy* **to tell MySQL** *to ignore foreign key checks?* (in a way that is also not simultaneously incompatible with sqlite)? – Michael - sqlbot Apr 16 '16 at 06:00

2 Answers2

2

if you are user mysql, you can connect to mysql and use SET GLOBAL FOREIGN_KEY_CHECKS = 0; delete the db table you want, and again SET GLOBAL FOREIGN_KEY_CHECKS = 1;. This value verifies foreign relationships in the db tables.

evandrix
  • 6,041
  • 4
  • 27
  • 38
Doosik Bae
  • 71
  • 4
-2

First of all, why would you want to ignore foreign key constraints? When you define your foreign key you can pass a string with the foreign table and column names, and those will be resolved correctly even if the foreign table hasn't been created yet.

But in any case, if you want to have foreign keys that are not enforced, just don't define your foreign key columns as foreign keys, define them as simple columns and manage the foreign key dependency yourself. Not a good idea, in my opinion, but it can be done.

Miguel Grinberg
  • 65,299
  • 14
  • 133
  • 152
  • 1
    If I don't add "SET foreign_key_checks = 0" - I cannot create the tables when running db migrate script using upgrade using MySQL. I don't understand "When you define your foreign key you can pass a string with the foreign table and column names", this is how I define foreign key: class Author(db.Model): ... paper_id = db.Column(db.Integer, db.ForeignKey('papers.id')) – dami.max Apr 18 '16 at 16:15
  • @harryw Do you have a `papers` table in the same migration? – Miguel Grinberg Apr 18 '16 at 16:53
  • yes, but authors table is the one to be created first in the migration script without papers being created later - this is fine for sqlite but not for mysql. thx! – dami.max Apr 18 '16 at 17:54
  • the error I got: sqlalchemy.exc.IntegrityError: (_mysql_exceptions.IntegrityError) (1215, 'Cannot add foreign key constraint') – dami.max Apr 18 '16 at 23:13