Python peewee foreign key constraint incorrectly formed

Question

I'm currently working on a python project using peewee to connect to MySQLDatabase.
If I want to create a table using database.create_tables(tables=[]) (create_table does not work)
I get the following error message from my logger:

ERROR session[5924]: (1005, 'Can't create table example_database.example_table (errno: 150 "Foreign key constraint is incorrectly formed")')

example_table is specified as:

class Example_Table(BaseModel):
    id = PrimaryKeyField()
    example_table2 = ForeignKeyField(Modul)
    class Meta:
        db_table = 'Example_Table'

BaseModel is defined as follows:

class BaseModel(Model):
    class Meta:
        database = database

and database is my MySQLDatabase Object.
The Problem is, why doesnt the foreign key constraint work and why are the tables all saved in Lowercase, but I defined them in Uppercase

If I run the program again, It creates the tables but gives me a duplicate key_name error

Version: peewee==3.0.17

coleifer coleifer · Accepted Answer · 2018-02-13T14:43:18

So, in peewee 3.x, you use table_name rather than db_table to explicitly declare a non-default table name.

I ran some sample code locally to give a test:

class User(Model):
    username = TextField()
    class Meta:
        database = db
        table_name = 'UserTbl'

class Note(Model):
    user = ForeignKeyField(User, backref='notes')
    content = TextField()
    class Meta:
        database = db
        table_name = 'NoteTbl'

I created the tables and checked the SQL output, which looks correct to me:

CREATE TABLE IF NOT EXISTS `UserTbl` (`id` INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY, `username` TEXT NOT NULL)
CREATE TABLE IF NOT EXISTS `NoteTbl` (`id` INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY, `user_id` INTEGER NOT NULL, `content` TEXT NOT NULL, FOREIGN KEY (`user_id`) REFERENCES `UserTbl` (`id`))
CREATE INDEX `note_user_id` ON `NoteTbl` (`user_id`)

Hope that helps.

Python peewee foreign key constraint incorrectly formed

1 Answers