12

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. :-)

Improve this question
1
  • In order to avoid duplicate answers, maybe this answer can put you on the right track? Commented Jun 24, 2011 at 16:03

3 Answers 3

Reset to default
11

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

CREATE UNIQUE INDEX uidxName ON mytable (myfield COLLATE NOCASE)

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 con.close()

Additional python documentation for sqlite3 shown here

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

4

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"

Improve this answer

1 Comment

Interesting, but COLLATE NOCASE only provides case insensitivity for ASCII letters. No matter, I'm only using sqlite for the prototype and not the final product. Thanks for the suggestion though.
2

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 = [ models.UniqueConstraint( Lower('<field name>'), name='<constraint name>' ), ]
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.