How to make a case-insensitive unique column in SQLite

pdo, sqlite

Solution

`COLLATE NOCASE` is your friend:

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

Problem

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?

Original source