PeeWee ForeignKeyField error. IntegrityError: (1215, 'Cannot add foreign key constraint')

Question

I have code like below:

import datetime

import peewee as pw
import os

my_db = pw.MySQLDatabase(database=os.getenv("DB_NAME"), host=os.getenv("DB_HOST"), port=os.getenv("DB_PORT"),
                            user=os.getenv("DB_USER"), passwd=os.getenv("DB_PASSWORD")


class MySQLModel(pw.Model):
    """A base model class that will use MySQL"""

    class Meta:
        database = my_db


class UnsignedBigAutoField(pw.BigAutoField):
    field_type = 'BIGINT UNSIGNED UNIQUE AUTO_INCREMENT'


class UnsignedAutoField(pw.AutoField):
    field_type = 'INT UNSIGNED UNIQUE AUTO_INCREMENT'


class User(MySQLModel):
    id = UnsignedBigAutoField(primary_key=True)
    alias = pw.CharField(max_length=16, unique=True, null=False)
    date_created = pw.DateTimeField(default=datetime.datetime.now)


class Ticket(MySQLModel):
    id = UnsignedBigAutoField(primary_key=True)
    user = pw.ForeignKeyField(User, backref="tickets", field="id", on_delete="RESTRICT", on_update="CASCADE")
    date_created = pw.DateTimeField(default=datetime.datetime.now)

my_db.create_tables([User, Ticket])

The last line of the code when creating ForeignKey produces error:

peewee.IntegrityError: (1215, 'Cannot add foreign key constraint')

I checked the datatype(Bitint), and unique/non-null flag are all setup correctly for User table.

Couldn't figure out what went wrong here. I created the ForeignKey in MySqlWorkBench manually in Ticket table without a problem.

Any help would be appreciated.

You might try removing the `UNIQUE` from the `field_type` - that should be unnecessary because these are primary keys, which are by definition unique. — coleifer, May 02 '23 at 12:54
@coleifer removing the UNIQUE does remove the unique flag for the attribute. But this doesn't change the result anyway. Still facing the same issue — EzyHoo, May 02 '23 at 17:23

score 1 · Accepted Answer · answered May 03 '23 at 12:54

It is probably not recommended to put the UNIQUE in the field definitions since these are already primary keys:

class UnsignedBigAutoField(pw.BigAutoField):
    field_type = 'BIGINT UNSIGNED AUTO_INCREMENT'

class UnsignedAutoField(pw.AutoField):
    field_type = 'INT UNSIGNED AUTO_INCREMENT'

The issue is that Peewee sees your subclass of BigAutoField and infers that it should use BIGINT instead of BIGINT UNSIGNED. The fix is to override the field_type on the foreign-key, e.g.:

class UnsignedForeignKey(pw.ForeignKeyField):
    field_type = 'BIGINT UNSIGNED'

class Ticket(MySQLModel):
    id = UnsignedBigAutoField(primary_key=True)
    user = UnsignedForeignKeyField(User, backref="tickets", field="id", on_delete="RESTRICT", on_update="CASCADE")
    date_created = pw.DateTimeField(default=datetime.datetime.now)

PeeWee ForeignKeyField error. IntegrityError: (1215, 'Cannot add foreign key constraint')

1 Answers1