How to add a unique insensitive constraint on a peewee model?

Question

I would like to add a unique insensitive constraint to the following model using peewee orm with a sqlite database

import peewee as p

db = p.SqliteDatabase(':memory:')

class Player(p.Model):
   name = p.CharField()
  
   class Meta:
      database = db

I would like to prevent adding 'joe' and 'Joe' as player name in the table. As the field is case sensitive, a unique constraint is not enough.

Thank you for your ideas !

You can for this post about same problem : https://stackoverflow.com/questions/20914946/how-to-make-a-case-insensitive-unique-column-in-sqlite — Ptit Xav, Mar 05 '21 at 13:13
Thank you for your answer, I didn't know about collate. I will look into it — Louc, Mar 05 '21 at 15:22

score 4 · Accepted Answer · answered Mar 05 '21 at 14:44

4

You can specify arbitrary constraints in the Meta.constraints list:

from peewee import *

db = SqliteDatabase(':memory:')

class K(Model):
    key = TextField()
    class Meta:
        constraints = [SQL('UNIQUE ("key" COLLATE NOCASE)')]
        database = db

db.create_tables([K])

K.create(key='k1')
K.create(key='K1') # Fails

answered Mar 05 '21 at 14:44

coleifer

24,887
6
60
75

This code is working as I expect. Thank you ! – Louc Mar 05 '21 at 15:26

score 0 · Answer 2 · answered Jan 01 '22 at 03:15

For reference, syntax for creating a case insensitive unique constraint on two fields would be:

class Group(BaseModel):
    name = CharField()
    category = CharField()

    class Meta:
        constraints = [SQL('UNIQUE("name" COLLATE NOCASE, "category" COLLATE NOCASE)')]

How to add a unique insensitive constraint on a peewee model?

2 Answers2