I have a many-to-many relationship between two models, User and Role, implemented via an intermediary table, UserRoleThrough, which has two ForeignKeyFields: one referencing User, and another referencing Role. As I understand from the docs, ON DELETE functionality is supported by initializing ForeignKeyField with an on_delete argument. Though it's not very clear what values on_delete can take, the documentation gives an example, e.g. 'CASCADE'. This being said, on_delete='CASCADE' seems to have no effect, as attempting to delete a row from one of the parent tables throws an error.
peewee.IntegrityError: FOREIGN KEY constraint failed
Inspecting the generated schema with a database browser reveals that the foreign keys were not declared with ON DELETE.
CREATE TABLE "userrolethrough" (
"id" INTEGER NOT NULL PRIMARY KEY,
"user_id" INTEGER NOT NULL,
"role_id" INTEGER NOT NULL,
FOREIGN KEY ("user_id") REFERENCES "user" ("id"),
FOREIGN KEY ("role_id") REFERENCES "role" ("id")
)
So what I'm doing wrong here? How do I get on_delete to work? Here's a minimal reproducible example using Python 3.6 and Peewee 3.0.2.
import peewee
db_proxy = peewee.Proxy()
class BaseModel(peewee.Model):
class Meta:
database = db_proxy
class User(BaseModel):
name = peewee.CharField()
class Role(BaseModel):
name = peewee.CharField()
class UserRoleThrough(BaseModel):
user = peewee.ForeignKeyField(User, on_delete='CASCADE')
role = peewee.ForeignKeyField(Role, on_delete='CASCADE')
if __name__ == '__main__':
db = peewee.SqliteDatabase('test.db')
db.pragma('foreign_keys', 1, permanent=True)
db_proxy.initialize(db)
tables = [
User,
Role,
UserRoleThrough
]
db.create_tables(tables)
isaac = User.create(name='Isaac')
admin = Role.create(name='Admin')
UserRoleThrough.create(user=isaac, role=admin)
User.delete().execute()
