SQL: On Conflict Do Nothing

Welcome to the On Conflict Do Nothing lesson!

This lesson is shown as static text below. However, it's designed to be used interactively. Click the button below to start!

Suppose that we want to track whether the cats have been vaccinated. Vaccination only needs to happen once, so we don't need to track a vaccination count. If a cat exists in the vaccinations table, then they've been vaccinated. We'll use a UNIQUE constraint to make sure that the same cat doesn't exist in the table more than once.

exec(`
  CREATE TABLE vaccinations (cat_name TEXT UNIQUE NOT NULL);
  INSERT INTO vaccinations (cat_name) VALUES ('Ms. Fluff');
  SELECT * FROM vaccinations;
`);

Result:

[{cat_name: 'Ms. Fluff'}]

The fact that Ms. Fluff is in the vaccinations table tells us that she's been vaccinated. But what if she's accidentally vaccinated a second time, and we try to insert her again? (Remember that the cat_name column has a UNIQUE constraint.)

exec(`
  CREATE TABLE vaccinations (cat_name TEXT UNIQUE NOT NULL);
  INSERT INTO vaccinations (cat_name) VALUES ('Ms. Fluff');
  INSERT INTO vaccinations (cat_name) VALUES ('Ms. Fluff');
`);

Result:

Error: UNIQUE constraint failed: vaccinations.cat_name

This isn't what we want! She was already vaccinated, so vaccinating her again shouldn't change this table.
We could use JavaScript's catch to catch the error, but there's a better way. SQL allows us to specify what happens when there's a conflict (that is, when a constraint is violated).
The simplest thing that we can do is nothing at all: when there's a conflict, we ignore it. In SQL terms, we tell the database ON CONFLICT, the column that might conflict (cat_name), and what to do (DO NOTHING). The insert is aborted, so nothing in the database changes.

exec(`
  CREATE TABLE vaccinations (cat_name TEXT UNIQUE NOT NULL);
  INSERT INTO vaccinations (cat_name) VALUES ('Ms. Fluff');
  INSERT INTO vaccinations (cat_name) VALUES ('Ms. Fluff')
    ON CONFLICT (cat_name) DO NOTHING;
  SELECT * FROM vaccinations;
`);

Result:

[{cat_name: 'Ms. Fluff'}]

ON CONFLICT ... DO NOTHING is useful whenever you want to make sure that a certain row exists, but don't need to do anything if it already exists.
Imagine that we allow users to request deactivation of their accounts. To do that, they email support, and the support representative deactivates the account for them. We track the date that the account was deactivated.
Sometimes, a user will request account deactivation again because they forget that they're already deactivated. When that happens, we don't create another deactivation record, and we don't change the deactivation date. The support representative still tells our system to deactivate the account, but the system uses ON CONFLICT (...) DO NOTHING to ignore the request.
Here's a code problem:
Amir requested account deactivation on 2025-10-03. We deactivated his account as requested. Then he requested deactivation again on 2025-10-22. We want that request to be ignored, and it shouldn't change the deactivation date. Finish the final INSERT here to use ON CONFLICT to ignore the deactivation request if the account is already deactivated.
exec(`
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE deactivations (
user_id INTEGER UNIQUE REFERENCES users(id),
date TEXT NOT NULL
);
INSERT INTO users (name) VALUES ('Amir');
INSERT INTO deactivations (user_id, date) VALUES (1, '2025-10-03');
INSERT INTO deactivations (user_id, date) VALUES (1, '2025-10-22')
ON CONFLICT (user_id) DO NOTHING;
`);
exec(`SELECT * FROM deactivations`);
Goal:
[{user_id: 1, date: '2025-10-03'}]
Yours:
[{user_id: 1, date: '2025-10-03'}]
Show Author's Answer