Summary

Dolt rejects ON DELETE SET DEFAULT / ON UPDATE SET DEFAULT in CREATE TABLE:

error on line 2 for query CREATE TABLE child(... FOREIGN KEY(pv) REFERENCES parent(pk) ON DELETE SET DEFAULT): "SET DEFAULT" is not supported

MySQL's InnoDB also rejects SET DEFAULT, so this is plausibly intentional parity with MySQL. SQLite (and doltlite) accept and implement the action: when a parent row is deleted, the child FK column reverts to its column default.

Why this matters for doltlite parity

I'm running the doltlite vs Dolt oracle suite for cross-branch merges with cascading FK actions, and the ON DELETE SET DEFAULT case has to be a doltlite-only test because the schema fails to parse on Dolt. Filing this so the divergence is tracked; flagging it as low priority since MySQL behaves the same way.

Repro (Dolt)

CREATE TABLE parent(pk INTEGER PRIMARY KEY);
CREATE TABLE child(
  pk INTEGER PRIMARY KEY,
  pv INT DEFAULT 99,
  FOREIGN KEY(pv) REFERENCES parent(pk) ON DELETE SET DEFAULT
);
-- error: "SET DEFAULT" is not supported

Repro (doltlite — works)

PRAGMA foreign_keys=1;
CREATE TABLE parent(pk INTEGER PRIMARY KEY);
CREATE TABLE child(
  pk INTEGER PRIMARY KEY,
  pv INT DEFAULT 99,
  FOREIGN KEY(pv) REFERENCES parent(pk) ON DELETE SET DEFAULT
);
INSERT INTO parent VALUES (1),(2),(99);
INSERT INTO child VALUES (100,1),(200,2);
DELETE FROM parent WHERE pk=1;
SELECT pk, pv FROM child ORDER BY pk;
-- 100|99
-- 200|2

What to do

Either:

1. Implement SET DEFAULT in Dolt (matches SQLite/PostgreSQL behavior), or
2. Document this as intentional MySQL parity and add it to the list of known doltlite-only FK actions.

Found while adding FK-action merge coverage to doltlite's oracle suite at https://github.com/dolthub/doltlite/blob/master/test/vc_oracle_fk_merge_test.sh.