{"record":{"id":"6f9f8a1db29f115f","repo":"vitessio/vitess","slug":"vt12001-6f9f8a","errorCode":"VT12001","errorMessage":"ON DUPLICATE KEY UPDATE with foreign keys with unique keys","messagePattern":"ON DUPLICATE KEY UPDATE with foreign keys with unique keys","errorType":"panic","errorClass":null,"httpStatus":null,"severity":"error","filePath":"go/vt/vtgate/planbuilder/operators/upsert.go","lineNumber":82,"sourceCode":"\treturn inputs\n}\n\nfunc (u *Upsert) SetInputs(inputs []Operator) {\n\tu.Sources = nil\n\tu.setInputs(inputs)\n}\n\nfunc (u *Upsert) ShortDescription() string {\n\treturn \"\"\n}\n\nfunc (u *Upsert) GetOrdering(ctx *plancontext.PlanningContext) []OrderBy {\n\treturn nil\n}\n\nfunc createUpsertOperator(ctx *plancontext.PlanningContext, ins *sqlparser.Insert, insOp Operator, rows sqlparser.Values, vTbl *vindexes.BaseTable) Operator {\n\tif len(vTbl.UniqueKeys) != 0 {\n\t\tpanic(vterrors.VT12001(\"ON DUPLICATE KEY UPDATE with foreign keys with unique keys\"))\n\t}\n\n\tpIndexes, _ := findPKIndexes(vTbl, ins)\n\tif len(pIndexes) == 0 {\n\t\t// nothing to compare for update.\n\t\t// Hence, only perform insert.\n\t\treturn insOp\n\t}\n\n\tupsert := &Upsert{}\n\tfor _, row := range rows {\n\t\tvar comparisons []sqlparser.Expr\n\t\tfor _, pIdx := range pIndexes {\n\t\t\tvar expr sqlparser.Expr\n\t\t\tif pIdx.idx == -1 {\n\t\t\t\texpr = pIdx.def\n\t\t\t} else {\n\t\t\t\texpr = row[pIdx.idx]","sourceCodeStart":64,"sourceCodeEnd":100,"githubUrl":"https://github.com/vitessio/vitess/blob/01a25a7d176f94613b8d59d799f438380a8760e4/go/vt/vtgate/planbuilder/operators/upsert.go#L64-L100","documentation":"INSERT ... ON DUPLICATE KEY UPDATE against a table that has foreign keys and declared unique keys is unsupported. The upsert planning path requires PK comparison and cannot safely handle unique-key interactions with FK verification, so it panics with VT12001.","triggerScenarios":"createUpsertOperator is invoked for a table where vTbl.UniqueKeys is non-empty (table participates in FKs and has unique keys beyond the primary key) and the statement is an upsert.","commonSituations":"Tables with UNIQUE constraints used in upsert-heavy workloads after enabling managed foreign keys; migrating MySQL upserts to Vitess without revisiting unique indexes.","solutions":["Remove the extra unique keys from the table, keeping only the primary key","Perform the insert/update logic in the application: SELECT then INSERT or UPDATE explicitly","Avoid managed FK mode for this table, or restructure so the upsert targets a table without unique keys","If the unique key is unnecessary for correctness, drop it"],"exampleFix":"// before\nCREATE TABLE t (id INT PRIMARY KEY, email VARCHAR(64), UNIQUE KEY uq_email (email));\nINSERT INTO t ... ON DUPLICATE KEY UPDATE ...;\n// after\nCREATE TABLE t (id INT PRIMARY KEY, email VARCHAR(64)); -- upsert handled in app or unique key dropped","handlingStrategy":"validation","validationCode":"// before using upsert on a table with managed FKs\nif len(table.UniqueKeys) > 0 && usesOnDuplicateKeyUpdate(stmt) {\n    return fmt.Errorf(\"upsert unsupported for %s: table has unique keys with FKs\", table.Name)\n}","typeGuard":null,"tryCatchPattern":"if err != nil && strings.Contains(err.Error(), \"ON DUPLICATE KEY UPDATE with foreign keys with unique keys\") {\n    // fall back to app-level select-then-insert/update\n}","preventionTips":["Keep tables used for upserts free of extra unique keys when FKs are managed","Implement insert-or-update logic in the application layer","Review unique indexes before enabling foreign_key_mode","Update Vitess: later releases may lift this restriction"],"tags":["vitess","vtgate","upsert","foreign-keys","unique-key"],"backgroundTag":"upsert-with-unique-key-unsupported","analyzedSha":"01a25a7d176f94613b8d59d799f438380a8760e4","analyzedAt":"2026-09-01T17:28:30.605Z","contentChangedAt":null,"schemaVersion":2},"datasetVersion":"2026-09-08T10:18:20.063Z"}