如何在同时具有主键和唯一列的表上执行向上插入

How to perform an upsert on a table with both a primary key and UNIQUE column(如何在同时具有主键和唯一列的表上执行向上插入)

问题描述

SQLite新手，正在尝试了解upsert功能。

我有一个包含以下DDL的表：

CREATE TABLE contacts (
    contact_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    phone TEXT NOT NULL UNIQUE
);

假设我插入一条记录：

INSERT INTO contacts (contact_id, first_name, last_name, email, phone)
VALUES (1, 'John', 'Jones', 'jjones@gmail.com', '888-867-5309');

我如何才能执行同时考虑唯一约束(email)和PK约束(contact_id)的upsert，以便处理这两种情况中的任何一种，因为我不知道哪个约束会失败。

我尝试过这样做：

INSERT INTO contacts (contact_id, first_name, last_name, email, phone)
VALUES (1, 'John', 'Jones', 'john.jones@gmail.com', '888-867-5309')
ON CONFLICT (contact_id, email) DO UPDATE
SET first_name='John', last_name='Jones', email='john.jones@gmail.com', phone='888-867-5309'
WHERE contact_id=1;

但我收到错误：

sqlite3.OperationalError：ON冲突子句与任何主键或唯一约束都不匹配

单独操作效果很好。

INSERT INTO contacts (contact_id, first_name, last_name, email, phone)
VALUES (1, 'John', 'Jones', 'john.jones@gmail.com', '888-867-5309')
ON CONFLICT (contact_id) DO UPDATE
SET first_name='John', last_name='Jones', email='john.jones@gmail.com', phone='888-867-5309'
WHERE contact_id=1;

INSERT INTO contacts (contact_id, first_name, last_name, email, phone)
VALUES (1, 'John', 'Jones', 'john.jones@gmail.com', '888-867-5309')
ON CONFLICT (email) DO UPDATE
SET first_name='John', last_name='Jones', email='john.jones@gmail.com', phone='888-867-5309'
WHERE email='john.jones@gmail.com';

我理解我得到这个错误是因为列的组合不满足单个约束，它包含两个约束。但我该如何将两者都考虑在内呢？

推荐答案

自您定义：

contact_id INTEGER PRIMARY KEY

contact_id为AUTOINCREMENT，在插入新行时，您必须不显式设置此列的值(尽管如果没有冲突，SQLite不会抱怨)。
因此，您只需：

INSERT INTO contacts (first_name, last_name, email, phone)
VALUES ('John', 'Jones', 'john.jones@gmail.com', '888-867-5309')
ON CONFLICT (email) DO UPDATE
SET first_name='John', last_name='Jones', email='john.jones@gmail.com', phone='888-867-5309'; 

但是，您还定义了：

phone TEXT NOT NULL UNIQUE

因此，表中有2个UNIQUE约束。
在这种情况下，如果希望SQLite处理这两列中的冲突，可以使用(INSERT OR) REPLACE：

REPLACE INTO contacts (first_name, last_name, email, phone)
VALUES('Johny', 'Jones', 'john.jones@gmail.com', '888-867-5309')

您必须知道，REPLACE会在没有冲突的情况下插入新行(在您的例子中是email和phone列)，但如果有冲突，则删除冲突的行(因为会有两个冲突行，一个冲突行email，另一个冲突phone行)并插入新行。

这篇关于如何在同时具有主键和唯一列的表上执行向上插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！