代码之家 › 专栏 › 技术社区 › nyarian

具有冲突策略的SQLite中的唯一列对约束

sqlite

1

nyarian · 技术社区 · 7 年前

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

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);

我的问题是:这两种方法是否等价,是否都能正确地实现所描述的目标,或者它们是否存在一些逻辑差异,这些差异会影响后续的重复插入逻辑?

1 回复 | 直到 7 年前

1

MikeT 7 年前

两种方法都创建一个索引,因此它们的行为方式相同( 见下文 ). 文件表明:-

在数据库中创建唯一索引。(异常为整数上的主键和主键,没有ROWID表)
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行。
这个

依据:-

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_自动索引 )