Index & foreign key failures: where they surface

🧪

Hands-on lab

Trigger a duplicate-key index failure and a foreign-key orphan on all four engines — then read exactly which phase caught each one.

Demos/Learn/course8-module-03 →

The deploy got all the way through the table structure. Columns added, types reshaped, everything green — and then it stopped on the index. A unique index, and two rows in the table share the value you just told the database has to be one of a kind.

That’s the 2am incident from Module 0. Time to diagnose it properly — and then meet its close cousin, the foreign key that finds an orphan after the data’s already landed.

Hey folks. I’m Forge Barrett, master of the Content Forge here at SchemaSmith.

This is Module 3 of Course 8. Structure’s behind us. Now we’re on the two phases that run after the tables are shaped — indexes and constraints, then foreign keys. Each one fails in its own way, and each one fails in a place that surprises people the first time. Let’s fix that.

The two post-structure phases

Once the tables are the right shape, convergence keeps going in order:

• MissingIndexesAndConstraintsQuench — creates every index, primary key, unique constraint, and check constraint that’s in your model but not yet on the table.
• ForeignKeyQuench — adds the foreign keys, and it runs after data delivery, not before.

In your Progress.log:

[localhost].[diag_keys]   Quenching indexes and constraints
[localhost].[diag_keys]   Quenching foreign keys

Two details about where these phases sit are the whole module. First: a changed index doesn’t fail where you changed it. Second: a foreign key is validated after the rows are already in the table. Hold those two and the error locations stop surprising you.

Beat 1 — a unique index over duplicate data

Here’s the setup. Customer already has an index on Email — a plain, non-unique one. Two customers signed up with the same address, ana.f@shop.test, and under a non-unique index that’s perfectly legal. Then someone tightens the model:

{ "Name": "[IX_Customer_Email]", "Unique": true, "IndexColumns": "[Email]" }

Flip that index to unique, deploy over the duplicates, and it stops.

Where it fails — and why it’s not where you’d think

You changed the index. So you’d expect the failure in the modified-tables phase, up in the structure half. It isn’t there. It’s one phase later, at Quenching indexes and constraints.

That’s the recreate-as-missing pattern, and it’s worth internalizing. SchemaSmith can’t alter an index in place — a changed index is dropped during the structure phases, which leaves it missing from the database even though it’s still in your model. So the very next index phase sees a missing index and rebuilds it — this time as UNIQUE. That rebuild is where it meets the two ana.f@shop.test rows and refuses.

A change to an index is a drop-and-recreate, and the recreate is a missing-index build. Read the phase, not the diff.

Three engines, three messages

Same duplicate data, same verdict, in the FAILED to quench: block of Progress.log.

SQL Server — exit 2:

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found
for the object name 'dbo.Customer' and the index name 'IX_Customer_Email'.
The duplicate key value is (ana.f@shop.test).

The underlying error is 1505 — the number isn’t printed, but the message names the table, the index, and the exact colliding value.

PostgreSQL — exit 2:

23505: could not create unique index "ix_customer_email"

SQLSTATE 23505 printed straight out. Terser, same judgment.

MySQL — exit 2:

Duplicate entry 'ana.f@shop.test' for key 'Customer.IX_Customer_Email'

The classic 1062 message. All three name the value that broke the build.

Reading the artifact

The phase drops a copy-runnable artifact — SchemaQuench - Quench Indexes …. It’s a single proc call:

EXEC [diag_keys].SchemaSmith.MissingIndexesAndConstraintsQuench @ProductName = 'Shop', @WhatIf = 0

Paste that into your client and the phase fails identically, in isolation — that’s your reproduction. There’s no >>> FAILING BATCH marker here; that’s a user-script thing, and this is a mechanical phase. The error is in Progress.log; the artifact is the one-line re-run.

The fix

The index definition is right — the data isn’t unique yet. Make it unique, then redeploy the same flip:

UPDATE Customer SET Email = 'ana.f7@shop.test' WHERE CustomerId = 7;

Redeploy. The unique index builds, and the leftover failure checkpoint clears on the way through — green on all four. The change to the schema was never the problem; the data hadn’t caught up to it.

Beat 2 — a foreign key over an orphan

Now the second phase. SalesOrder has an order that points at CustomerId 999 — a customer that doesn’t exist. Maybe that customer got deleted; maybe the row was imported loose. While there’s no foreign key, the database has no opinion about it. Then you add one:

{ "Name": "[FK_SalesOrder_Customer]", "Columns": "[CustomerId]", "RelatedTable": "[Customer]", "RelatedColumns": "[CustomerId]" }

Why it surfaces last

Foreign keys close the envelope. Convergence builds the structure, then delivers the data, and only then adds the foreign keys — so by the time ForeignKeyQuench runs, the rows are already resident. That’s deliberate: it lets data delivery seed child and parent rows in the same run without fighting key order. The cost is that an orphan never surfaces early. It sits quietly through every structure and data phase and only trips the wire at the very last stop, when the FK is validated against data that’s already in the table.

Exit 2, at Quenching foreign keys:

SQL Server:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
"FK_SalesOrder_Customer". The conflict occurred in database "diag_keys",
table "dbo.Customer", column 'CustomerId'.

Error 547.

PostgreSQL:

23503: insert or update on table "salesorder" violates foreign key constraint "fk_salesorder_customer"

MySQL:

Cannot add or update a child row: a foreign key constraint fails
(`diag_keys`.`#sql-…`, CONSTRAINT `FK_SalesOrder_Customer`
FOREIGN KEY (`CustomerId`) REFERENCES `Customer` (`CustomerId`))

Error 1452. The #sql-… name is MySQL’s shadow-copy table for the alter — not a table you own, just where the validation ran.

The copy-runnable artifact for this phase is SchemaQuench - Quench Foreign Keys …:

EXEC [diag_keys].SchemaSmith.ForeignKeyQuench @ProductName = 'Shop', @WhatIf = 0

The fix

The FK did its job — it caught a real integrity gap. Give the orphan a parent that exists (or delete it), then redeploy the same FK add:

UPDATE SalesOrder SET CustomerId = 1 WHERE CustomerId = 999;

Redeploy. The foreign key is created and trusted, on all four engines. Same shape as beat 1: the constraint was right, the data had to catch up.

Two locations to remember

Before you walk Progress.log: Failures.log names the failing phase and the error in one read — the fast entry point before the deeper trace this module covers.

You’ve now seen both failures land somewhere other than where the change was written:

• A changed index fails at the index phase, not the structure phase — because a change is a drop, and the rebuild is a missing-index build (recreate-as-missing).
• A foreign-key violation fails last, after the data is already in — because FKs are validated after delivery, on purpose.

Everything else is the method you already know: find the FAILED to quench: block, read the phase name, read the engine’s message, pick the fix. Locate, read, recover.

Check yourself: You flip an existing index to UNIQUE and the deploy fails — but not in the modified-tables phase where you'd expect a changed index to be handled. It fails at 'Quenching indexes and constraints'. Why?

Because SchemaSmith can’t alter an index in place. A changed index is dropped during the structure phases, which leaves it missing from the database while still present in your model. The next phase — MissingIndexesAndConstraintsQuench — sees a missing index and rebuilds it, now as UNIQUE, and that rebuild is where it meets the duplicate data and fails. This is the recreate-as-missing pattern: a change becomes a drop plus a missing-index recreate, so the failure surfaces one phase later than the diff suggests. Read the phase name in Progress.log, not just what you edited.

What’s next

Two phases left on the map, and then the toolkit:

• Module 4 · Script-slot & data-delivery failures — the user-supplied half: Before/After scripts, template slots, and a data delivery that fails on the rows themselves.
• Module 5 · The recovery toolkit — --ResumeQuench, marking a script done, and the full fix-and-continue playbook for when you can’t just redeploy from the top.

---

Every constraint you declare is a promise about the metal — this value stands alone, this row answers to that one. When a quench stops on an index or a foreign key, the forge isn’t failing you; it’s refusing to stamp a promise the data can’t keep. Fix the data, and the promise holds. That’s the difference between a constraint that’s decoration and one that’s load-bearing.

Got a unique index or a foreign key that stopped a deploy cold? Email me at forgebarrett@schemasmith.com — tell me the phase name and what the engine said, and we’ll trace it together.

Next up: Course 8 · Module 4 — Script-slot & data-delivery failures, where the failures come from the scripts and data you wrote.

Until then, may your keys stay unique and your references never dangle.

— Forge

Mark this lesson complete