CODEWITHSUNDEEP
sqlitepdo
Dan Goodspeed
Dan Goodspeed

Reputation: 3560

How to make a case-insensitive unique column in SQLite

I haven't been able to find the answer to this. I'm trying to create a table with a unique email address column. And when I do

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

when using PDO, I get the error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1 near "(": syntax error' in script.php:65 Stack trace: #0 script.php(65): PDO->exec('CREATE TABLE us...') #1 {main} thrown in script.php on line 65

Line 65 is the CREATE TABLE line. If I take out the UNIQUE, it works fine. Is there a better way of doing it?

Upvotes: 8

Views: 7784

Answers (2)

laalto
laalto

Reputation: 152847

COLLATE NOCASE is your friend:

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

Upvotes: 18

Alec Teal
Alec Teal

Reputation: 5918

For speed, make all your input lower case first, and use a normal unique column.

This is great for read-more-frequent-than-write use cases because queries just need to compare strings, rather than converting the field first which it must do lots of times to compare.

Upvotes: 3

Related Questions