Can you achieve a case insensitive 'unique' constraint in Sqlite3 (with Django)?

So let's say I'm using Python 2.5's built-in default sqlite3 and I have a Django model class with the following code:

class SomeEntity(models.Model):
    some_field = models.CharField(max_length=50, db_index=True, unique=True)

I've got the admin interface setup and everything appears to be working fine except that I can create two SomeEntity records, one with some_field='some value' and one with some_field='Some Value' because the unique constraint on some_field appears to be case sensitive.

Is there some way to force sqlite to perform a case insensitive comparison when checking for uniqueness?

I can't seem to find an option for this in Django's docs and I'm wondering if there's something that I can do directly to sqlite to get it to behave the way I want. :-)

Asked by: Catherine651 | Posted: 01-10-2021

Answer 1

Yes this can easily be done by adding a unique index to the table with the following command:


If you need case insensitivity for nonASCII letters, you will need to register your own COLLATION with commands similar to the following:

The following example shows a custom collation that sorts “the wrong way”:

import sqlite3

def collate_reverse(string1, string2):
    return -cmp(string1, string2)

con = sqlite3.connect(":memory:")
con.create_collation("reverse", collate_reverse)

cur = con.cursor()
cur.execute("create table test(x)")
cur.executemany("insert into test(x) values (?)", [("a",), ("b",)])
cur.execute("select x from test order by x collate reverse")
for row in cur:
    print row

Additional python documentation for sqlite3 shown here

Answered by: Lydia560 | Posted: 02-11-2021

Answer 2

Perhaps you can create and use a custom model field; it would be a subclass of CharField but providing a db_type method returning "text collate nocase"

Answered by: Fiona810 | Posted: 02-11-2021

Answer 3

For anyone in 2021, with the help of Django 4.0 UniqueConstraint expressions you could add a Meta class to your model like this:

class Meta:
    constraints = [
            Lower('<field name>'),
            name='<constraint name>'

Answered by: Elise715 | Posted: 02-11-2021

Similar questions

python - Unique field case insensitive constraint

I am trying to ensure that all tags are unique when converted to lowercase. However, when I run the migrate on the following model I receive the following error: api.Tag: (models.E012) 'constraints' refers to the nonexistent field 'Lower(F(name))'. class Tag(models.Model): name = models.CharField(max_length=30) class Meta: constraints = [ models....

python - Case insensitive urls for Django?

It seems by default django's url solver perform case sensitive search for solving url and differentiate between '/Login' and 'login'. My url patterns are as follows. urlpatterns = patterns('', (r'^admin/(.*)',, (r'^static/(?P&lt;path&gt;.*)$', 'django.views.static.serve', {'document_root': settings.STATIC_DOC_ROOT, 'show_indexes': True}), (r'^login/$', '

python - Case insensitive dictionary

I'd like my dictionary to be case insensitive. I have this example code: text = "practice changing the color" words = {'color': 'colour', 'practice': 'practise'} def replace(words,text): keys = words.keys() for i in keys: text= text.replace(i ,words[i]) return text text = replace(words,text) print text Output = practise changing the colour

python - How do I make this sorting case insensitive?

def sortProfiles(p): return sorted(p, key=itemgetter('first_name')) I have a list with dictionaries. This function allows me to sort them by their first_name. However, it's case-sensitive.

python - Case Insensitive Translation

I'm displaying certain strings in my app in some places as regular case and in some places as upper case: {% trans %} {% trans %} I'm specifying translations using the .po/.mo files: msgid "Welcome" msgstr "歓迎" And the translation seems to be case-sensitive. 'Welcome' gets translated to '歓迎' but 'WELCOME' does not get transl...

python - How can django sql queries use case insensitive and contains at the same time?

Suppose I have two users with username 'AbA' and 'aBa' in the database. My query word is 'ab'. I used User.objects.filter(username__contains='ab') and User.objects.filter(username__iexact='ab') These two queries get empty result. However, I want to use something like username__contains__iexact='ab' that can retrieve both 'AbA' and 'aBa'. ...

python - Querying MongoDB (via pymongo) in case insensitive efficiently

I'm currently creating a website in python (pyramid) which requires users to sign up and log in. The system allows for users to choose a username which can be a mixture of capital letters, lowercase letters, and numbers. The problem arises when making sure that two users don't accidentally share the same username, i.e. in my system 'randomUser' should be the same as 'RandomUser' or 'randomuser'. Unfortunat...

python - tkinter case insensitive bind

I've noticed that when you bind keys in tkinter (python3.2 winxp), the binds are case sensitive with the letter characters. In other words binding &lt;Control-o&gt; does not work if you press Control+o if caps lock is active. Does it mean I need to write two bindings for each case insensitive key combination with letter characters? Or is there any way to solve this? Thanks for help :)

python - Case insensitive unique model fields in Django?

I have basically a username is unique (case insensitive), but the case matters when displaying as provided by the user. I have the following requirements: field is CharField compatible field is unique, but case insensitive field needs to be searchable ignoring case (avoid using iexact, easily forgotten) field is stored with case intact preferably enforced on datab...

python - How to iterate over case insensitive sorted dictionary items?

Example: &gt;&gt;&gt; d = {'answer':1, 'Question':2} &gt;&gt;&gt; for i, j in sorted(d.items()): print i Question answer I would like case insensitive list: answer Question and I believe it can be done in simple Pythonic way.

string - Python case insensitive file name?

I need to load a file given it's name, but the name I get is case insensitive. "A.txt" could actually be "a.txt". How to do this the fast way (not generate all possible names and try each)?

Still can't find your answer? Check out these communities...

PySlackers | Full Stack Python | NHS Python | Pythonist Cafe | Hacker Earth | Discord Python