How to make a case-insensitive unique column in SQLite

Question

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?

score 18 · Accepted Answer · edited Nov 08 '17 at 21:15

18

COLLATE NOCASE is your friend:

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

edited Nov 08 '17 at 21:15

Spooky

2,966
8
27
41

answered Jan 04 '14 at 00:05

laalto

150,114
66
286
303

Sorry, if you find it irrelevant but having made a column like that unique, how to catch the case when user enters existing data and display a toast? – Nobody Jan 19 '17 at 23:01

score 3 · Answer 2 · answered Jan 04 '14 at 00:08

3

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.

answered Jan 04 '14 at 00:08

Alec Teal

5,770
3
23
50

1

It is better to do the enforcement within the database rather than in the clients performing the input, as you can't always decide where the data is coming from, like when you are importing the data from an external source. – vfclists Mar 09 '16 at 08:01
1

Alec Teal, you claim this is for speed, but have you benchmarked this? I did. Case A stored the original text and a separate derived field with lowercased text (unique constraint on lowercased field). Case B stored just the original and used unique constraint with COLLATE NOCASE. For case A, inserts were about 5-8% slower (more data) and searches were 15-20% slower than case B, and sorting was about even. – paddy Mar 28 '17 at 04:35
"searches were 15-20% slower than case B" did you add COLLATE NOCASE to the where condition as well? – Daniele Ricci Nov 14 '18 at 20:48
@DanieleRicci 85% of statistics are made up. – Alec Teal Nov 18 '18 at 11:40

How to make a case-insensitive unique column in SQLite

2 Answers2

Linked