sqlite - SQLite中的唯一列对约束与冲突策略

Question

我需要为一行中的两列创建一个唯一约束，并附加冲突策略。假设我们有一张 table :

CREATE TABLE `telephones`(
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
telephone STRING NOT NULL);

所以很明显，它是一个单独的表，用于用户和他的电话之间的一对多关系。我需要的是为 user_id 创建一个唯一索引和 telephone ，所以数据库不应该有重复。
AFAIK，这里有两种创建此类约束的方法:通过将索引创建为单独的 SQL 请求或通过在 CREATE TABLE 语句中创建约束。第一种方式如下所示:

CREATE UNIQUE INDEX `user_ids_and_telephones` ON `telephones`(`user_id`, `telephone`) ON CONFLICT IGNORE

第二种方式如下所示:

CREATE TABLE `telephones`(
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
telephone STRING NOT NULL,
UNIQUE(`user_id`, `telephone`) ON CONFLICT IGNORE);

我的问题是:这些方式是否等效，并且都可以针对所描述的目标正常工作，或者它们是否有一些逻辑差异会影响后续重复插入逻辑？

我没有发现文档对此很清楚。

Answer 1

两种方式都会创建一个索引，因此它们的行为方式相同( 见下文 )。该文档将其声明为:-

In most cases, UNIQUE and PRIMARY KEY constraints are implemented by creating a unique index in the database. (The exceptions are INTEGER PRIMARY KEY and PRIMARY KEYs on WITHOUT ROWID tables.) Hence, the following schemas are logically equivalent:

CREATE TABLE t1(a, b UNIQUE);

CREATE TABLE t1(a, b PRIMARY KEY);

CREATE TABLE t1(a, b);
CREATE UNIQUE INDEX t1b ON t1(b);

SQL As Understood By SQLite - CREATE TABLE - SQL Data Constraints

然而 ，我不相信您可以在独立定义索引时编写冲突子句。所以
CREATE UNIQUE INDEX user_ids_and_telephones ON telephones(user_id, telephone) ON CONFLICT IGNORE无效。

因此，冲突处理会​​有所不同。

例如考虑以下内容:-

DROP TABLE IF EXISTS `telephones1`;
CREATE TABLE IF NOT EXISTS `telephones1`(
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
telephone STRING NOT NULL);
DROP INDEX IF EXISTS user_ids_and_telephones;
CREATE UNIQUE INDEX `user_ids_and_telephones` ON `telephones1`(`user_id`, `telephone`)
    -- ON CONFLICT IGNORE commented out as is invalid
;

DROP TABLE IF EXISTS `telephones2`;
CREATE TABLE IF NOT EXISTS `telephones2`(
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
telephone STRING NOT NULL,
UNIQUE(`user_id`, `telephone`) ON CONFLICT IGNORE);

SELECT * FROM sqlite_master WHERE type = 'index' AND name LIKE '%telephones%';


INSERT INTO `telephones2` VALUES
    (null,1,'phone1'),(null,2,'phone2'),(null,3,'phone1'),(null,1,'phone1');
INSERT INTO `telephones1` VALUES
    (null,1,'phone1'),(null,2,'phone2'),(null,3,'phone1'),(null,1,'phone1');

插入到电话 2 中不会失败，但只会插入 4 行中的 3 行。

这
插入到电话1 失败，没有插入任何行。

按照 :-

DROP TABLE IF EXISTS `telephones1`
> OK
> Time: 0.389s


CREATE TABLE IF NOT EXISTS `telephones1`(
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
telephone STRING NOT NULL)
> OK
> Time: 0.31s


DROP INDEX IF EXISTS user_ids_and_telephones
> OK
> Time: 0s


CREATE UNIQUE INDEX `user_ids_and_telephones` ON `telephones1`(`user_id`, `telephone`)
    -- ON CONFLICT IGNORE
> OK
> Time: 0.366s


DROP TABLE IF EXISTS `telephones2`
> OK
> Time: 0.383s


CREATE TABLE IF NOT EXISTS `telephones2`(
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
telephone STRING NOT NULL,
UNIQUE(`user_id`, `telephone`) ON CONFLICT IGNORE)
> OK
> Time: 0.358s


SELECT * FROM sqlite_master WHERE type = 'index' AND name LIKE '%telephones%'
> OK
> Time: 0s


INSERT INTO `telephones2` VALUES
    (null,1,'phone1'),(null,2,'phone2'),(null,3,'phone1'),(null,1,'phone1')
> Affected rows: 3
> Time: 0.356s


INSERT INTO `telephones1` VALUES
    (null,1,'phone1'),(null,2,'phone2'),(null,3,'phone1'),(null,1,'phone1')
> UNIQUE constraint failed: telephones1.user_id, telephones1.telephone
> Time: 0.004s

从 slqite_master 的查询输出可以看出，实际上创建了两个索引:-



附在 上的那个电话2 是一个自动生成的索引(即它以 sqlite_autoindex 开头)