Unique indexes and database rejection

The final uniqueness invariant belongs in storage

A pre-insert query can improve an error message, but it cannot make a later insert unique when other writers can intervene. The model therefore declares a genuine unique Sku index and tests the storage rejection. That rule also needs a clear equality policy: case, normalization and collation can change which values collide. This lesson uses one identical SKU twice, avoiding an unverified promise about every provider’s case rules. Treat uniqueness, optimistic concurrency and user-input validation as separate mechanisms rather than labeling every failure to save as the same conflict.

Inspect a provider-specific unique violation and stored result

The external fixture creates its schema from a model with HasIndex(...).IsUnique, saves Original under R1 and then attempts Duplicate under the same SKU in a new context. Its catch filter accepts only a DbUpdateException containing SQLite error 19 with extended unique-index classification 2067. A fresh context verifies one row named Original. This establishes that the actual schema rejected the duplicate and did not replace the original. It does not make those numeric codes portable to another database, nor does it imply that an unrelated save failure should be converted into a friendly duplicate-SKU result.

using System;
using System.Linq;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

await using var connection = new SqliteConnection("Data Source=:memory:;Foreign Keys=True");
await connection.OpenAsync();
var options = new DbContextOptionsBuilder<LabDb>().UseSqlite(connection).Options;
await using (var db = new LabDb(options)) { await db.Database.EnsureCreatedAsync(); db.Items.Add(new Item { Name = "Original", Sku = "R1" }); await db.SaveChangesAsync(); }
bool duplicate = false;
await using (var db = new LabDb(options))
{
    db.Items.Add(new Item { Name = "Duplicate", Sku = "R1" });
    try { await db.SaveChangesAsync(); }
    catch (DbUpdateException ex) when (ex.InnerException is SqliteException { SqliteErrorCode: 19, SqliteExtendedErrorCode: 2067 }) { duplicate = true; }
}
await using (var verify = new LabDb(options)) { Check(duplicate && await verify.Items.CountAsync() == 1 && (await verify.Items.SingleAsync()).Name == "Original", "unique index preserves original"); }
Console.WriteLine("unique:rejected"); Console.WriteLine("rows:1");

static void Check(bool condition, string name) { if (!condition) throw new InvalidOperationException("CHECK: " + name); }

Trace an ordinal input policy without claiming a race-safe constraint

The .NET9 set accepts R1, rejects an identical R1 and accepts lowercase r1 under StringComparer.Ordinal. Its True, False, True transcript describes the stated application equality rule. No database, transaction or competing writer is present. If the production database uses another collation, this early check can disagree with the final constraint and must be designed accordingly. That possibility is a reason to align normalization and constraint design, not to remove relational enforcement. Keep the portable check useful as an input rule while retaining a real provider test for the stored invariant.

Separate a rejected insert from a stale update

A unique insert failure is generally surfaced as a provider-specific error wrapped in DbUpdateException, whereas an optimistic-concurrency failure concerns an expected update or delete affecting no matching row. Do not catch DbUpdateConcurrencyException and assume it handles duplicate inserts. After a rejected save, the offending entity may still be tracked as Added, so simply calling SaveChanges again on that same unit can repeat the error. The fixture ends the failed context and verifies with another. A production adapter should translate only the known constraint it can identify and leave unrelated integrity or availability failures visible.

Practice: test a second index and preserve failure classification

Add a separately named unique index for another field in a fresh schema fixture. Trigger each constraint independently and decide whether the adapter can distinguish the intended domain errors from the provider information it receives. Avoid matching localized exception text as the only contract. Then test two differently cased SKUs under the actual chosen collation and compare with the portable ordinal policy. These are provider-backed experiments; a HashSet test cannot establish them. Keep the initial identical-key rejection as a baseline and verify the original stored row after each rejected unit.

• Hint: translate the specific invariant your operation owns, not every DbUpdateException.
• Hint: end or repair failed tracking state before another save attempt.

Practice: define normalization before deduplicating a batch

Create a supplied batch containing exact duplicates, case variants and surrounding whitespace. Choose whether the application preserves or normalizes each value, then test that policy independently with ordinal comparisons after normalization. Return row-specific duplicate information without silently dropping an entry whose omission would change a total. Explain which race remains after this input validation and why the database index is still necessary. The exercise should produce a domain rule, not fake SQLite exceptions or an invented EF namespace that merely returns the values expected by the lesson.

• Hint: normalization is part of identity, not an incidental formatting step.
• Hint: early validation cannot observe a later competing write.

Keep the real index and its provider evidence reviewable

The genuine SQLite result is a rejected duplicate with one original row retained. The BCL transcript is a case-sensitive supplied-key rule. Neither validates a production migration’s index creation or another provider’s error codes and collation. A useful review names the indexed columns, normalization, schema state, known exception classification and failed-unit handling. These details are especially important in the capstone, where a duplicate reservation reference must reject an attempted stock change atomically rather than producing a plausible success from a partial application-level check.