如何在SQLite中创建不区分大小写的唯一列 [英] 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))
)

使用PDO时，出现错误:

when using PDO, I get the error:

致命错误:带有消息'SQLSTATE [HY000]的未捕获异常'PDOException':一般错误:script.php:65中(":语法错误附近有1个堆栈跟踪:#0 script.php(65):PDO -> exec('CREATE TABLE us ...')#1 {main}在第65行的script.php中抛出

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

第65行是CREATE TABLE行.如果我拿出UNIQUE，它可以正常工作.有更好的方法吗?

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

推荐答案

COLLATE NOCASE 是您的朋友:

COLLATE NOCASE is your friend:

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

这篇关于如何在SQLite中创建不区分大小写的唯一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！