Create Unqiue Case-Insensitive Constraint on Two Varchar Fields

Create Unqiue case-insensitive constraint on two varchar fields

Assuming your table is called person, and the first and last name columns are called first_name and last_name, add this unique constraint:

ALTER TABLE person ADD CONSTRAINT person_name_unique
    UNIQUE(LOWER(first_name),LOWER(last_name));

Let me know if I understood your question correctly and made the correct assumptions about your table layout.

How do you impose a case insensitive unique constraint on a Firebird field value?

You can add a unique index on lower of email column on the table like so:

create unique index email_unq_idx on agent computed by (lower(email));

How to make case insensitive constraints?

Define the column with a case-insensitive collation:

CREATE TABLE customer(
  name varchar(32) collate utf8_general_ci NOT NULL,
  dob date NOT NULL,
  unique (name, dob)
);

By default MySQL is case-insensitive, so I assume your server or database has set the collation to a case-sensitive collation.

Here is a db<>fiddle.

Deferrable, case-insensitive unique constraint

You can circumvent the restriction by using the special type citext provided by the additional module of the same name. Quoting the manual:

The citext module provides a case-insensitive character string type,
citext. Essentially, it internally calls lower when comparing values.
Otherwise, it behaves almost exactly like text.

It addresses your case exactly. Run once per database:

CREATE EXTENSION citext;

Then you can:

CREATE TABLE sample_table ( 
   my_column citext
  ,CONSTRAINT my_unique_constraint UNIQUE(my_column)
   DEFERRABLE INITIALLY IMMEDIATE
);

Unique field case insensitive constraint

Since django-4.0, you can work with expressions in a UniqueConstraint [Django-doc], so:

class Tag(models.Model):
    name = models.CharField(max_length=30)
    
    class Meta:
        constraints = [
            models.UniqueConstraint(
                Lower('name'),
                name='unique_name'
            )
        ]

How to make a case-insensitive unique column in SQLite

COLLATE NOCASE is your friend:

CREATE TABLE users (
  email TEXT PRIMARY KEY,
  password TEXT NOT NULL CHECK(password<>''),
  UNIQUE (email COLLATE NOCASE)
)

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

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

---

Related Topics