5

New to SQLite and trying to understand the upsert functionality.

I have a table with the following 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
);

Let's say I insert a record:

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

How can I do an upsert that takes into account both the UNIQUE constraint (email) and the PK constraint ( contact_id) so that it handles either case since I don't know which constraint will fail.

I tried doing this:

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;

But I get the error:

sqlite3.OperationalError: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint

Doing them individually works just fine.

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

I understand I get the error because the combination of columns doesn't meet a single constraint, it encompasses two. But how would I take both into account?

forpas
  • 160,666
  • 10
  • 38
  • 76
TomNash
  • 3,147
  • 2
  • 21
  • 57

1 Answers1

7

Since you defined:

contact_id INTEGER PRIMARY KEY

contact_id is AUTOINCREMENT and you must not set explicitly a value for this column when you insert a new row (although SQLite would not complain if you do if there is no conflict).
So all you need is:

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

But, you defined also:

phone TEXT NOT NULL UNIQUE

so there are 2 UNIQUE constraints in your table.
For this case if you want SQLite to deal with conflicts from both columns you can use (INSERT OR) REPLACE:

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

You must know that REPLACE inserts the new row if there is no conflict (for the columns email and phone in your case), but if there is a conflict then deletes the conflicting row or rows (because there would be 2 conflicting rows one for email and the other for phone) and inserts the new row.

forpas
  • 160,666
  • 10
  • 38
  • 76