SQLite的新手,并试图理解更新插入功能。
我有一个包含以下 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');
我怎样才能做一个同时考虑 UNIQUE 约束 (email
( 和 PK 约束 (contact_id
( 的更新插入,以便它处理任何一种情况,因为我不知道哪个约束会失败。
我尝试这样做:
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.操作错误:ON CONFLICT 子句与任何主键或唯一约束都不匹配
单独做它们就可以了。
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')
您必须知道,如果没有冲突(对于列email
和phone
,则REPLACE
插入新行,但如果存在冲突,则会删除冲突的行或行(因为会有 2 个冲突行,一个用于email
,另一个用于phone
(并插入新行。