Django username uniqueness depends on database collation

Answer

Yes – Django’s enforcement of uniqueness for USERNAME_FIELD follows the database collation. PostgreSQL’s default collation is case‑sensitive, while MySQL’s default collation is typically case‑insensitive, so the same model behaves differently on the two backends.

Likely explanation

The uniqueness check is performed by a database‑level unique constraint (or index). Django’s UniqueValidator merely mirrors that constraint; it does not add its own case‑insensitive logic. Therefore, if the column collation treats 'User' and 'user' as distinct values, PostgreSQL will allow both, whereas MySQL will reject the second insert.

Confirmed facts

• Django’s built‑in UniqueValidator runs a case‑sensitive filter(username=value) query.
• A unique constraint on the column obeys the column’s collation.
• PostgreSQL provides the citext extension or a nondeterministic ICU collation for true case‑insensitive columns; MySQL offers case‑insensitive collations such as utf8mb4_0900_ai_ci.
• A functional unique index on lower(username) yields case‑insensitive uniqueness on both backends, but Django’s migration autodetector does not detect it, so it must be added via RunSQL.
• Storing a lower‑cased username (e.g., in save() or clean()) together with a regular unique constraint works identically on PostgreSQL and MySQL, at the cost of losing the original casing.

Recommended steps for this case

1. Decide whether you can change the column collation or install extensions.
   • If you can run CREATE EXTENSION citext; on PostgreSQL or choose a case‑insensitive collation when creating the MySQL table, alter the column to that type and keep Django’s default unique=True. This requires no extra indexes or application logic.
   • If collation changes are not possible, proceed to step 2.

1. Add a functional unique index via a migration.

   # migrations/0002_add_username_ci_index.py
   from django.db import migrations
   
   class Migration(migrations.Migration):
       dependencies = [
           ('your_app', '0001_initial'),
       ]
   
       operations = [
           migrations.RunSQL(
               sql="""
               CREATE UNIQUE INDEX username_ci_idx
               ON auth_user (lower(username));
               """,
               reverse_sql="""
               DROP INDEX username_ci_idx;
               """,
           )
       ]
   

   Keep the model’s username = models.CharField(unique=True) so Django’s ORM still generates the usual constraint; the functional index adds the case‑insensitive guard.

1. If you prefer an application‑only solution and can accept lower‑cased storage:

   # models.py
   from django.contrib.auth.models import AbstractUser
   
   class User(AbstractUser):
       def clean(self):
           self.username = self.username.lower()
           super().clean()
   
       def save(self, *args, **kwargs):
           self.username = self.username.lower()
           super().save(*args, **kwargs)
   

   Run migrations as usual; the unique constraint will now treat 'User' and 'user' as duplicates because both are stored as 'user'.

Verification

1. Create a test project with both PostgreSQL and MySQL services.
2. Apply the migration that adds the functional index (or alters the column collation).
3. In a Django shell, try to create two users: User.objects.create(username='Tester') followed by User.objects.create(username='tester').
4. Both backends should raise an IntegrityError on the second create, confirming case‑insensitive uniqueness.

Missing diagnostic detail

If you cannot change the collation or install citext, please confirm whether you are willing to store usernames in lower case (losing original casing). This determines whether the functional‑index route or the lower‑casing‑at‑save route is appropriate for your project.