SQLite NOCASE Can Admit Names Your App Treats as Duplicates

Unique name constraints in databases rely on a collation sequence to decide which values are equal. By default, SQLite’s built-in COLLATE NOCASE will treat ASCII letters case-insensitively, folding “ABC” and “abc” together. However, if an application’s policy for name equality is stronger, such as full Unicode normalization and case folding, SQLite’s NOCASE may admit distinct strings that the app considers duplicates. In this article, we define an explicit canonicalization policy (canon(raw) = NFC(casefold(NFC(raw)))) for project-tag names, then test whether SQLite’s unique constraint (with COLLATE NOCASE) actually enforces that policy.

We show how to audit the existing data for collisions, and how to enforce the stronger policy via a stored generated column. The focus is on preserving all original record IDs (no silent deletes or survivor selection), detecting any collisions by ID, and rolling back or refactoring safely if the schema cannot yet enforce the policy. This is not a general-purpose name matching tutorial; it is a contract audit and repair playbook for backend engineers or DBAs.

Declare what equal project-tag names mean

First, explicitly declare the application’s name-equality rule. Our chosen namespace policy is “identity through normalization+casefold”:

canon(raw) = NFC(casefold(NFC(raw))).

That means: take the raw input string, normalize it to Unicode Normal Form C (NFC), apply Unicode full casefold(), then normalize again to NFC to re-compose any accents. This policy preserves accent marks and spacing exactly, but ignores case and canonical composition differences. For example, “Café” and “Café” (an “e” + combining accent) should be considered equal (they both canonicalize to “café”), and “Straße” and “STRASSE” also collapse (canon = “strasse”). But “Cafe” (no accent) is distinct from “Café” under our rule (that pair canon = “cafe” vs “café”). We emphasize: this is an application-defined rule for project-tag strings, not a universal human-name equivalence.

With this policy, a unique key constraint must prevent inserting any two distinct rows whose canon() keys are equal. A database enforcing UNIQUE on the string column under only COLLATE NOCASE does not meet this requirement, because SQLite’s NOCASE collates only ASCII letters (A–Z) case-insensitively. In other words, SQLite NOCASE will fold “Alpha” and “ALPHA” (ASCII letters) to equal, but it will not fold “équipe” and “ÉQUIPE” equivalently (those rely on full Unicode casefolding beyond ASCII). Nor will SQLite’s NOCASE treat “Stra\u00dfe” (German sharp S) as equivalent to “STRASSE” (which is two ASCII S characters).

Therefore the existing schema constraint (e.g. CREATE TABLE projects(... raw_name TEXT COLLATE NOCASE UNIQUE)) is only an ASCII-case-insensitive uniqueness check. We must audit which actual stored records collide under our NFC+casefold rule. A collision means two distinct IDs whose canon(raw) results are identical. We do not delete duplicates, we simply detect them as unresolved until the policy is enforced (by renaming or other approved fixes).

For example, under our rule:

1. “Alpha” (ID 1) canon=“alpha”

2. “ALPHA” (ID 2) canon=“alpha”, so IDs 1 and 2 collide by policy.

3. “\u00c9quipe” (Équipe, ID 3) and “\u00e9quipe” (équipe, ID 4) both canon=“équipe”: a collision group {3, 4}.

4. “Cafe\u0301” (Café, decomposed accent, ID 5) and “Caf\u00e9” (Café, composed accent, ID 6) both canon=“café”: group {5, 6}.

5. “Stra\u00dfe” (Straße, ID 7) and “STRASSE” (ID 8) both canon=“strasse”: group {7, 8}.

6. “東京” (Tokyo in Kanji, ID 9) canon=“東京”, standalone (no case).

Those groups and their canonical keys are determined independently of SQLite, using our declared canon(). We keep a separate table of expected canonical results (alpha, équipe, café, strasse, 東京都 etc.) to validate our function.

Importantly, even if two rows have the same canon() key, we do not assume one record can be deleted. Each row (and its ID) is a distinct entity in the database. Enforcing uniqueness in the future means preventing insertion of duplicates, not automatically dropping existing ones. The unique constraint’s role is a write-time gate, not a post-hoc deduplicator. We will preserve both entries in each collision group, marking them as unresolved until an authorized fix is applied.

Our audit approach is thus:

1. List all existing records with the raw names and their canon() values (according to our policy), then group by identical canon().

2. Identify collision groups (more than one ID in a group).

3. Keep all IDs safe (no merges or deletes) but label collisions for resolution.

This sets up the acceptance criteria: after creating an enforcement mechanism, every ID must remain, each raw name must still match either its original or an explicitly renamed value, and canonical keys must exactly match expectations. If the existing schema admitted any pair as one unique row that our policy says are duplicates, we detected it in step 1. (If a pair is not admitted by SQLite’s NOCASE, that doesn’t bypass our policy test.) We ensure fairness: e.g. “Cafe” is not treated equal to “Café” because our rule preserves the accent, so we do not collapse that group.

By decoupling policy (application’s canon()) from SQLite’s default, we avoid misinterpreting SQLite’s guarantees. As SQLite documentation notes, “NOCASE... uses sqlite3_strnicmp(), folding only the 26 ASCII upper-case letters to lower-case”. Thus any non-ASCII or composed characters are not folded. Our separate normalization ensures a fully deterministic canonicalization before comparison.

Learn more about fundamental database administration foundations and how schema design, including generated-column constraints, drives such policies in large projects.

Freeze SQLite, Python and the Unicode policy

We pin the exact software versions and settings used for testing, since even small Unicode or SQLite version changes could alter results. Our testing environment was:

• SQLite engine: version 3.46.1 (built with default options).

• Python: version 3.13.5 (CPython) with sqlite3 module.

• Unicode database: version 15.1.0 (as per unicodedata.unidata_version in Python 3.13.5).

• Normalization policy: NFC (Unicode Normalization Form C) from Python’s unicodedata.normalize.

• Case folding: Python’s str.casefold() (which implements full Unicode folding including special mappings like ß→ss).

• Text encoding: we use UTF-8 for all text stored.

• SQLite transaction mode: default (“DEFERRED” transactions; implicit if not autocommit).

• Journal mode: WAL for safety (though not material to logic here).

• trusted_schema: we assume the default (which is OFF, meaning user-defined functions can appear in schema). Our test DB is disposable and “trusted,” but note that on a hardened system with PRAGMA trusted_schema=ON;, using a custom UDF in a generated column would be disallowed. We do not recommend changing security settings for the real schema; if UDF use is prevented, the migration must be reconsidered with the security team.

We create a local (in-memory or temp file) SQLite database for this experiment with two tables:

• The original table projects(id INTEGER PRIMARY KEY, raw_name TEXT NOT NULL COLLATE NOCASE UNIQUE). This matches the production schema: raw_name is UNIQUE with NOCASE.

• A shadow table (empty at first) with columns (id INTEGER PRIMARY KEY, raw_name TEXT NOT NULL, name_key TEXT COLLATE BINARY GENERATED ALWAYS AS (canon_name(raw_name)) STORED NOT NULL UNIQUE). This enforces uniqueness on the canonical key, storing it in a BINARY (exact) form for deterministic comparison. The function canon_name(X) is a user-defined function we register in Python to implement NFC(casefold(NFC(X))).

In code, we register canon_name as:

import sqlite3, unicodedata, hashlib
def canon_name(x: str) -> str:
    # (1) reject if null or contains NUL (per lab rules)
    if x is None or '\x00' in x:
        raise ValueError("Disallowed input")
    # (2) apply NFC normalization, casefold, then NFC again
    step1 = unicodedata.normalize('NFC', x)
    folded = step1.casefold()
    key = unicodedata.normalize('NFC', folded)
    return key

con = sqlite3.connect(":memory:")
con.create_function("canon_name", 1, canon_name, deterministic=True)
cur = con.cursor()
cur.execute("CREATE TABLE projects(id INTEGER PRIMARY KEY, raw_name TEXT NOT NULL COLLATE NOCASE UNIQUE)")

We mark the function as deterministic=True, which tells SQLite it can assume the function always returns the same output for given input. (However, determinism in this context is an optimization hint and does not magically protect against changes in the Python or Unicode version across time.) We should store a version identifier or check that the function code and Unicode database used are consistent across all writers. A simple approach is to embed a version string or hash in the database (not shown here) and have writers verify it before writes.

Trust but verify: marking a function deterministic is a promise. The database will cache computed values for optimization, but if the implementation of canon_name changes (say Python version or Unicode table updated), old cached values or stored keys could become invalid. Thus we must record the function’s implementation digest or version in a table or comment and refuse writes if mismatched.

Because our environment is trusted (we set trusted_schema OFF for testing), we can create the generated column referencing canon_name. In a hardened production system, if trusted_schema is ON, SQLite would block this CREATE TABLE; then the schema approach must be reconsidered (see below under HOLD/unresolved). For now, we proceed:

CREATE TABLE shadow (
    id INTEGER PRIMARY KEY,
    raw_name TEXT NOT NULL,
    name_key TEXT COLLATE BINARY 
        GENERATED ALWAYS AS (canon_name(raw_name)) STORED NOT NULL UNIQUE
);

This ensures any insert to shadow must supply raw_name, and name_key is computed (case-sensitive binary) and must be unique. Note: COLLATE BINARY means exact byte-for-byte comparison, no further folding.

The key point: a regular stored column with a unique index could be manually overridden or de-synced from raw_name. Using a generated column prevents discrepancy: you cannot manually write name_key, and SQLite itself will maintain it. (It’s not writable, and our tests will prove assigning it is disallowed.)

If the SQLite version was older than 3.31.0, generated columns wouldn’t exist, but our test uses 3.46.1 which supports them. This feature is required for a record-preserving schema enforcement; we cannot use triggers because they would have to handle pre-existing rows and be more complex, while we want a single column UNIQUE constraint.

Keep the original table unchanged in this lab for comparison, and do not swap it into production yet. We’re only validating the shadow schema and the data migration logic.

Seed literal Unicode cases with independent record IDs

Next, load the nine test cases in the original table. We use Python parameter binding to avoid quoting issues, but for brevity here’s the conceptual SQL (executed one row at a time to see each result):

sqlite> CREATE TABLE projects(id INTEGER PRIMARY KEY, raw_name TEXT NOT NULL COLLATE NOCASE UNIQUE);
sqlite> INSERT INTO projects(id, raw_name) VALUES (1, 'Alpha');
sqlite> INSERT INTO projects(id, raw_name) VALUES (2, 'ALPHA');

On the second insert, SQLite reports a unique-constraint violation:

Error: UNIQUE constraint failed: projects.raw_name

This happens because under NOCASE, 'Alpha' and 'ALPHA' compare equal, so ID 2 is refused. We did not catch or IGNORE it in code; it was an immediate error. That’s expected baseline: ASCII-casefold considers them the same, so SQLite enforces uniqueness on ID 1 vs 2.

Continue with the others (IDs 3–9):

sqlite> INSERT INTO projects(id, raw_name) VALUES (3, '\u00c9quipe');
sqlite> INSERT INTO projects(id, raw_name) VALUES (4, '\u00e9quipe');
sqlite> INSERT INTO projects(id, raw_name) VALUES (5, 'Cafe\u0301');
sqlite> INSERT INTO projects(id, raw_name) VALUES (6, 'Caf\u00e9');
sqlite> INSERT INTO projects(id, raw_name) VALUES (7, 'Stra\u00dfe');
sqlite> INSERT INTO projects(id, raw_name) VALUES (8, 'STRASSE');
sqlite> INSERT INTO projects(id, raw_name) VALUES (9, '\u6771\u4eac');

All of these are accepted with no error in SQLite (NOCASE does not consider them equal pairs). The only conflict was ID 2. After insertion, we have IDs 1, 3, 4, 5, 6, 7, 8, 9 present.

To document the stored bytes (so we’re not fooled by visual similarity), query each row’s raw name and its hex representation (UTF-8):

sqlite> SELECT id, raw_name, hex(raw_name) AS hex_bytes FROM projects ORDER BY id;

Expected result (showing actual bytes):

(Column 3 shows the UTF-8 hex. For example, “\u00c9quipe” is 0xC3 0x89 0x71 0x75 0x69 0x70 0x65.)

Notably:

1. IDs 3 and 4 differ by the first two bytes (C389 vs C3A9), reflecting É vs é.

2. IDs 5 and 6 differ (CC81 vs C3A9): that’s decomposed vs composed accent.

3. IDs 7 and 8 differ entirely (Stra\u00dfe vs STRASSE): one includes byte C39F (ß) vs ASCII sequence.

This confirms SQLite did store the bytes as provided (no silent re-normalization). We now have a dataset to check against our canonical-key policy.

We also added control checks: for instance, the string 'cafe' (no accent) would hex 63616665, which is not among these, so it wouldn’t collide with ‘café’. Indeed, our policy keeps “cafe” ≠ “café”. (We did not insert “cafe” as a record, but we note that under NFCasefold, 'Cafe\u0301' (café) and 'Cafe' would not collide because 'cafe' casefolds to “cafe”.) This reassures that only accent-insensitive matches we expect are captured (not accent-stripping).

Measure what built-in NOCASE actually rejects

Now we examine what the original table’s UNIQUE constraint did. As we saw, only “Alpha”/“ALPHA” collided under NOCASE. To be thorough, we query that table:

sqlite> SELECT id, raw_name FROM projects;

This should list 8 rows (IDs 1 and 3–9). We already saw ID 2 was blocked. No other INSERT gave an error. So for the original schema:

1. Admitted IDs: 1, 3, 4, 5, 6, 7, 8, 9.

2. Rejected ID: 2 (duplicate ASCII-case of ID 1).

We explicitly expected ID 2 to fail under NOCASE. The three collision groups from our policy ({3, 4}, {5, 6}, {7, 8}) were not blocked by SQLite, since those pairs are not ASCII-casefold duplicates. (Indeed, {3, 4} involve É vs é, {5, 6} involve accent composition, {7, 8} involve ß; none are ASCII uppercase collisions.) This confirms: built-in NOCASE did exactly what its docs say: it only caught ASCII letters, not our stronger rule. So SQLite did not violate its documented behavior; it simply enforces a weaker uniqueness than we need.

The SQLite documentation confirms: “NOCASE... uses sqlite3_strnicmp()... only ASCII characters are case folded. SQLite does not attempt to do full UTF case folding”. Therefore, our expectation matches its mechanics. For example, “Stra\u00dfe”.lower() in ASCII remains “straße”, which is different from “strasse” from “STRASSE”.

If we query the table, we might present something like:

sqlite> SELECT hex(raw_name), raw_name FROM projects;

but as above, we’ve already listed bytes. The key takeaway: exactly one row (ID 2) was prevented, exactly the expected ASCII case duplicate.

Hence the baseline observation:

1. Admitted IDs: {1, 3, 4, 5, 6, 7, 8, 9}.

2. SQLite NOCASE collided (refused) only IDs 1 and 2.

3. Our policy collisions {3, 4}, {5, 6}, {7, 8} remain separate.

Documenting this confirms the gap. We do not yet change the original table; it remains a point of comparison.

By contrast, uniqueness and normalization requirements in data warehouses or SQL correctness often assume explicit LOWER() or NFKC. See SQLite’s UNIQUE constraint documentation and Refonte’s SQL correctness foundations for related context.

Compare bytes when identical-looking names differ

To emphasize that visually similar names are indeed different under the hood, we compare the byte sequences for the composed/decomposed accent examples. For the “Cafe\u0301” vs “Caf\u00e9” pair (IDs 5 and 6), the hex output above shows they differ at the accent:

• ID 5 (“Café” decomposed): 43 61 66 65 CC 81

• ID 6 (“Café” precomposed): 43 61 66 C3 A9

Though both display as “Café”, SQLite stored them differently. We rely on the hex output (or a direct byte comparison via hex()) to be sure we’re not mis-seeing them. This reinforces our use of canonicalization: normalizing to NFC makes them both “Caf\u00e9” under the hood, so their canon() matches. Similarly, for ID 7 vs 8 (Straße vs STRASSE), their hex is completely different (one includes C3 9F; the other is all ASCII).

This also shows why we must use hex or explicit code-point checks rather than font or case differences to detect equality. The canonicalizer will collapse them correctly, but the raw stored values are distinct bytes.

Reconcile application collisions without choosing survivors

Apply our declared policy to the admitted records {1, 3, 4, 5, 6, 7, 8, 9}. We compute the canonical key for each raw string (using our Python logic) and compare to the expected table we prepared. The table of expected canonicals (from our rule) is:

We generate these by Python (or trust our reasoning). For example, using Python’s canon_name function:

names = {1: "Alpha", 3: "\u00c9quipe", 4: "\u00e9quipe", 
         5: "Cafe\u0301", 6: "Caf\u00e9", 7: "Stra\u00dfe", 8: "STRASSE", 9: "\u6771\u4eac"}
for id, name in names.items():
    key = canon_name(name)
    print(id, name, "→", key)

Expected prints:

1 Alpha → alpha
3 Équipe → équipe
4 équipe → équipe
5 Café → café
6 Café → café
7 Straße → strasse
8 STRASSE → strasse
9 東京 → 東京

Crucially, IDs 3 and 4 both yield "équipe"; 5 and 6 both "café"; 7 and 8 both "strasse". IDs 1 and 9 are unique. We do not combine or delete any rows; we just note these as unresolved groups. So our collision groups under the policy are exactly {3, 4}, {5, 6}, {7, 8}.

We should audit that these match our predictions. We could do in Python:

expected = {3:"équipe", 4:"équipe", 5:"café", 6:"café", 7:"strasse", 8:"strasse", 1:"alpha", 9:"東京"}
for id, exp in expected.items():
    assert canon_name(names[id]) == exp, f"Mismatch on ID {id}"

All assertions pass in our test, confirming our logic is correct.

In practice, in production audit code we would cross-check every admitted row’s key against a known map of allowed collisions. If there were any other unexpected equality (or if an unadmitted row was actually a duplicate by our rule, which here isn’t the case), we would flag an error. Here, no surprises: each admitted key is as anticipated. We also verify our policy does not collapse anything outside those groups. For example, “Cafe” (no accent) would canon=“cafe”, which does not match any existing canon in this data set; hence no false positives.

Thus at this point:

1. Original table still has the 8 rows unchanged.

2. We have identified exactly the 3 collision pairs needing special handling.

3. The original table’s NOCASE constraint has not prevented inserting any of the policy-duplicates (IDs 4, 6, 8 were inserted fine despite colliding with 3, 5, 7 under our rule).

Because each collision group has two rows, we cannot enforce uniqueness by only deleting one or picking a survivor; both must be preserved. The policy violation is on insertion of duplicates, not an argument to merge them now. The approved fix (below) will be to rename one row in each group as a separate, allowed value rather than dropping it. These renames will be made only with explicit owner consent for each ID; this is why we named it a “rename manifest,” not an algorithmic merge.

For more on handling collisions and merge vs preserve decisions in integration, see how schema and application boundaries are bridged in backend database integration practices.

Enforce a canonical key through a generated column

To enforce our uniqueness policy on future writes, we alter the schema for new data writes. We already defined a shadow table with a generated column name_key that holds canon_name(raw_name). Recall:

CREATE TABLE shadow (
    id INTEGER PRIMARY KEY,
    raw_name TEXT NOT NULL,
    name_key TEXT COLLATE BINARY GENERATED ALWAYS AS (canon_name(raw_name)) STORED NOT NULL UNIQUE
);

We created this table earlier (using our registered function). Because name_key is generated, it is automatically computed on INSERT or UPDATE of raw_name. The COLLATE BINARY means the unique constraint compares the UTF-8 binary of the key (no extra folding or locale effects).

Why not just use a normal writable column? A freely writable derived-key column is unsafe: an application or DBA could write inconsistent data that violates canon(), defeating the constraint. With GENERATED ALWAYS, SQLite itself will enforce that name_key = canon_name(raw_name). It is impossible to insert a row where name_key doesn’t match the function applied to raw_name. (An attempt to do so will be rejected by SQLite as an illegal assignment to a generated column.) Thus, the contract that name_key is exactly the canonical form of raw_name is embedded in the schema.

We explicitly do not attempt to override the built-in NOCASE collation or load an ICU extension. We do not treat NOCASE as “broken” to be corrected by patching it. Instead, we keep NOCASE in the original table (only for legacy reads) and use our own collation on the new key (binary, so fully precise). This works in current SQLite and Python without requiring system changes.

Note: In SQLite, any column constraint UNIQUE or NOT NULL can be applied to a generated column. Here we declared name_key... UNIQUE and NOT NULL. This ensures no two rows in shadow can have the same canonical key. The UNIQUE is implemented as an index just as usual. SQLite requires that generated columns use scalar deterministic functions. We have marked canon_name as deterministic, satisfying this requirement.

Our SQL to set this up (in Python) was:

cur.execute("""
    CREATE TABLE shadow (
        id INTEGER PRIMARY KEY,
        raw_name TEXT NOT NULL,
        name_key TEXT COLLATE BINARY
          GENERATED ALWAYS AS (canon_name(raw_name)) STORED
          NOT NULL UNIQUE
    )
""")

No errors: this engine supports stored generated columns (≥3.31). Now shadow is empty, ready to test loading data under the new contract.

Do not weaken schema security just to run a UDF: If trusted_schema=ON, this CREATE TABLE would fail, because SQLite treats user-defined functions as untrusted by default. We have avoided toggling that. In a strict deployment, using a UDF in a table definition might require adding the function name to a "trusted function" list or an extension. If that’s not allowed, the migration must be on HOLD (see decision matrix) and replaced by a solution sanctioned by policy (maybe computing and storing a key outside the database, or using an ICU collation). That scenario is outside this lab’s scope, but worth noting: we assume the DBA has authority to allow this UDF or will coordinate with security to permit it safely.

Reject an unapproved shadow load and roll it back

With the shadow schema in place, we attempt to load all eight accepted records (IDs 1, 3, 4, 5, 6, 7, 8, 9) in one transaction, using their original raw values. This simulates “shadow migration” before cutover. We do not ALTER the original table; we fill the separate shadow table for testing. In Python:

shadow_vals = [(id, names[id]) for id in [1,3,4,5,6,7,8,9]]
try:
    cur.execute("BEGIN;")
    cur.executemany("INSERT INTO shadow(id, raw_name) VALUES(?, ?)", shadow_vals)
    cur.execute("COMMIT;")
except Exception as e:
    print("Insert failed:", e)
    cur.execute("ROLLBACK;")

We expect the INSERT sequence to fail partway because of our collisions. Specifically, inserting ID 3 with raw “Équipe” stores key “équipe”, which is fine as the first occurrence. But inserting ID 4 “équipe” yields the same key “équipe” again. At that point, SQLite raises a UNIQUE constraint error on shadow.name_key. Because we did all inserts in one explicit transaction, the error aborts the transaction. We catch it and roll back. The expected error message is something like:

sqlite3.IntegrityError: UNIQUE constraint failed: shadow.name_key

(This indicates the duplicate key "équipe" blocked the insert.)

After rollback, the transaction ensures no partial data remains. The shadow table is still empty, since the insert group was one transaction (BEGIN...ROLLBACK). We verify:

sqlite> SELECT COUNT(*) FROM projects;  -- original table
-- result: 8 (IDs 1,3-9 remain, as before)
sqlite> SELECT COUNT(*) FROM shadow;
-- result: 0 (rollback confirmed shadow is empty)

Thus, any constraint violation aborted the entire batch, leaving no half-migrated state. This is why we used BEGIN and caught the exception; if we had used executescript or multiple separate transactions, partial success could mislead us. Instead, each BEGIN/ROLLBACK ensures atomicity.

No rows were lost in the original table (we never touched it here), and no rows got into shadow. We have proven the original remained intact and the shadow migration aborted on collision as intended. We did not use any ignore/replace hacks: one violation and we rolled everything back.

The rejection is expected and by design: it signals that this migration as-is is not allowed. We cannot “fix” it by dropping a row automatically, since that would break the no-loss guarantee. Instead, the system responded correctly: one violation (ID 4 or ID 6 or ID 8, any of the duplicates) triggers an abort.

In summary:

1. Attempted to load all approved raw names into shadow.

2. Unique key violated at first collision (likely when ID 4 or ID 6 or ID 8 inserted after their partner).

3. Entire batch rolled back; shadow remains empty.

4. Original table still has the same 8 rows (IDs {1, 3, 4, 5, 6, 7, 8, 9}).

5. Conclusion: The shadow schema cannot accept existing rows without resolving collisions. We must rename one of each colliding pair first.

Apply only the approved synthetic rename manifest

The project owners have authorized exactly three renames to break the collisions, keyed by ID and old value (to avoid any mismatch). The approved manifest is:

1. ID 4: change raw_name “\u00e9quipe” → “\u00e9quipe-b”.

2. ID 6: change raw_name “Caf\u00e9” → “Café-b” (we’ll use capital C since original was capital).

3. ID 8: change raw_name “STRASSE” → “STRASSE-b”.

All other IDs (1, 3, 5, 7, 9) keep their original raw_name. These names with the -b suffix are synthetic placeholders to show a change. (In a real migration, owners might choose legitimate distinct names.)

We must insert exactly eight rows again, now with these changes. We still use the same explicit BEGIN/ROLLBACK block to ensure any mistake aborts all. In Python:

approved = {
    4: "\u00e9quipe-b",  # "équipe-b"
    6: "Café-b",
    8: "STRASSE-b"
}
shadow_vals2 = []
for id in [1,3,4,5,6,7,8,9]:
    raw = approved.get(id, names[id])
    shadow_vals2.append((id, raw))
try:
    cur.execute("BEGIN;")
    cur.executemany("INSERT INTO shadow(id, raw_name) VALUES(?, ?)", shadow_vals2)
    cur.execute("COMMIT;")
    print("Shadow load successful, 8 rows inserted.")
except Exception as e:
    print("Insert failed:", e)
    cur.execute("ROLLBACK;")

We should see no error this time. Each name_key will be unique:

• ID 1: key "alpha"

• ID 3: "équipe"

• ID 4: "équipe-b"

• ID 5: "café"

• ID 6: "café-b"

• ID 7: "strasse"

• ID 8: "strasse-b"

• ID 9: "東京"

No two of those keys match (we appended -b to break each duplication). The commit completes successfully.

Verify by selecting:

sqlite> SELECT id, raw_name, name_key FROM shadow ORDER BY id;

Should list exactly:

Everything is as planned. (One can also query hex(name_key) to see the UTF-8 of each key to double-check accents, but they’re straightforward here.)

We should also show that any attempt to violate uniqueness now fails, and that one cannot assign the name_key manually:

• Duplicate insert rejection: Try inserting a row that repeats an existing name_key. For example:

sqlite> INSERT INTO shadow(id, raw_name) VALUES (10, 'Alpha');

The raw_name 'Alpha' would canonize to "alpha", which collides with ID 1’s key "alpha". This should fail:

Error: UNIQUE constraint failed: shadow.name_key

Similarly, any update that causes a duplicate key fails. This ensures the constraint is working.

• Manual name_key assignment rejection: Try including name_key in insert:

sqlite> INSERT INTO shadow(id, raw_name, name_key) VALUES (11, 'Test', 'something');

SQLite does not allow writing to a generated column. The expected error is:

Error: cannot insert into generated column "name_key"

(Exact wording may vary. In Python it might be sqlite3.OperationalError). The point is that this action is rejected, so name_key cannot be faked.

These tests confirm the shadow table’s constraints:

-- Trying to manually set name_key (should fail)
INSERT INTO shadow(id, raw_name, name_key) VALUES (11, 'Ghost', 'ghost');

-- Trying a duplicate raw_name (allowed by itself) but duplicate key
INSERT INTO shadow(id, raw_name) VALUES (12, 'ALPHA');  -- fails key="alpha" dup

-- Trying a duplicate id (PK) and raw_name
INSERT INTO shadow(id, raw_name) VALUES (1, 'Different');

The first and second produce errors, the third also errors (PRIMARY KEY), so we skip that test as PK is obvious. The key point: any insertion that would violate either the primary key or the unique name_key is caught. The database enforces the contract fully.

Finally, we ensure that IDs 1, 3, 4, 5, 6, 7, 8, 9 are present exactly with their new raw names and computed keys. The original table projects is still untouched. We have not swapped or renamed it; that step is only approved after testing, and we consider it a separate migration.

We now have:

1. A validated shadow with all 8 IDs, preserving identity.

2. No two rows have the same name_key (by design).

3. Each stored name_key matches the independent calculation.

4. Any attempt to break it is refused.

At this point the shadow schema and data implement the new uniqueness contract. However, as a process, we haven’t declared “success” to roll out; we need to check versioning and unresolved issues next. But the raw mechanics of the constraint seem satisfied.

By contrast, simply creating a case-insensitive index or using triggers could accidentally skip row removal, but only a generated column ensures a strict function-based key. This exemplifies a best practice in database schema design using a registered deterministic function.

Reconcile all approved raw names and stored keys

With the shadow table loaded, let’s explicitly compare each row’s stored raw_name and generated name_key to the expected values (the manifest and canonical keys we designed). Query:

sqlite> SELECT id, raw_name, name_key, hex(name_key) FROM shadow ORDER BY id;

This should yield exactly 8 rows. We already wrote the human-readable expected table above. Checking each:

• ID 1: raw_name "Alpha", name_key "alpha" (hex 616C706861). Matches expectation.

• ID 3: raw "Équipe", name_key "équipe" (hex C3A97175697065).

• ID 4: raw "équipe-b", name_key "équipe-b" (hex C3A971756970652D62).

• ID 5: raw "Café", name_key "café" (hex 636166C3A9).

• ID 6: raw "Café-b", name_key "café-b" (hex 636166C3A92D62).

• ID 7: raw "Straße", name_key "strasse" (hex 73747261737365).

• ID 8: raw "STRASSE-b", name_key "strasse-b" (hex 737472617373652D62).

• ID 9: raw "東京", name_key "東京" (hex E69DB1E4BAAC).

Each hex confirms that the stored name_key is the correct lowercased/normalized form in UTF-8. We have not simply relied on a count or a vague match; these checks confirm every ID’s values individually. No ID is missing or duplicated, and keys match exactly the policy output (including the -b suffix in hex, confirming it is part of the key for 4, 6, 8).

We deliberately keep the original table for comparison. For example, checking projects still shows ID 4’s old “équipe” vs shadow now has “équipe-b”. For example:

sqlite> SELECT raw_name FROM projects WHERE id=4;
-- returns 'équipe'
sqlite> SELECT raw_name FROM shadow WHERE id=4;
-- returns 'équipe-b'

Thus, the original table is unchanged, our shadow is separate.

This step is the essence of validation: the shadow reflects exactly the approved outcome. All IDs 1–9 (except the rejected 2) appear once, with agreed names and keys. No data is lost or merged. The difference is only the values we specified in the rename manifest. This proves the script “worked as expected.” There were no surprises (no additional duplicates, no invalid key, no missing row).

In automated testing, one would perhaps diff the shadow’s data against the expected manifest. Here we did it manually. The following table records the shadow rows as evidence:

All match. No row count summary is needed beyond this explicit mapping, since that ensures identity and key equality are correct.

Thus the shadow is ready, from a data perspective.

Refonte’s SQLite backup validation playbook illustrates the value of verifying individual records. Here we do an equivalent check for our migration fixture.

Test rejected duplicate writes and reopened connections

Now that the shadow schema is filled with approved data, test the enforcement on new writes and after reopening the DB:

• Duplicate insert/update rejection:

As noted, any insert that generates a duplicate name_key should fail. We demonstrated inserting a row with raw "Alpha" again (or a variant that casefolds to "alpha") fails. Likewise, updating an existing raw_name to something that collides should fail:

sqlite> UPDATE shadow SET raw_name='ALPHA' WHERE id=1;

This would attempt to change ID 1's key from "alpha" to still "alpha", conflicting with itself or other row. The result:

Error: UNIQUE constraint failed: shadow.name_key

Or, try an insert:

sqlite> INSERT INTO shadow(id, raw_name) VALUES (10, 'Straße');

"Straße" casefolds to "strasse", which ID 7 already has, so it also fails with UNIQUE constraint. These errors prove the uniqueness is actively enforced.

• Generated-column override refusal:

As before, attempts to set name_key directly are refused:

sqlite> INSERT INTO shadow(id, raw_name, name_key) VALUES (11, 'X', 'x');
Error: cannot insert into generated column "name_key"

This message (or sqlite3.OperationalError) indicates the engine prevents manual writes to the generated field. Good: name_key can only be produced by the function.

• Closing and reopening the DB:

We close the Python connection (or the sqlite3 handle) and open a new one to the same file. Initially, do not register the canon_name function in the new connection. Then attempt to insert a new valid row:

sqlite> CREATE TABLE projects(id INTEGER PRIMARY KEY, raw_name TEXT COLLATE NOCASE UNIQUE);  -- reuse original
sqlite> ATTACH DATABASE 'test.db' AS testdb;
sqlite> INSERT INTO testdb.shadow(id, raw_name) VALUES (10, 'NewName');

If we run this without the function defined, SQLite cannot compute the generated column. It will error:

Error: no such function: canon_name

(Depending on context, might also say "cannot find function in generated column".) The key is that without the UDF registered in Python, the schema cannot be used for writing. The shadow table schema refers to canon_name, so SQLite requires that symbol. This shows the writer must have the right function installed to use the new table schema.

Now register the same canon_name function in the new connection, ensuring its code and environment are identical. Then retry:

sqlite> con.create_function("canon_name", 1, canon_name, deterministic=True)
sqlite> INSERT INTO shadow(id, raw_name) VALUES (10, 'AnotherOne');

This should now succeed (assuming key is unique). We skip detailed output, but indeed having the function loaded means the schema now works. Reading from shadow will show all prior rows plus the new one (id=10).

After reopening, we also verify that the previously inserted rows (IDs 1–9) are still present. Nothing changed by reopening. For completeness, we SELECT again:

sqlite> SELECT id, raw_name, name_key FROM shadow ORDER BY id;

It lists IDs 1–9 as before (plus any new ones if inserted successfully).

This confirms that:

• Reading the shadow table (with generated values pre-computed) does not require the function to be present. The stored name_key values are materialized in the table and can be fetched normally.

• Writing to it (inserting/updating) does require the function. Without it, the engine rejects the attempt.

Finally, we simulate a policy-version mismatch: Suppose the UDF code or Unicode data changed. If our canon_name algorithm changed (or even if the Python runtime uses a different Unicode version), a row’s stored key might not match the new calculation. For example, if Python’s Unicode DB had different casefolding for ß (unlikely across 15.1), it could diverge. To handle this, one could store the current unicodedata.unidata_version (which here is 15.1.0) and a hash of the canon_name code in a table. Each writer on start would check those against its own environment. If mismatch, the writer should halt with an error, because previous keys might not be valid under the new function.

We do not implement this check here, but note it as a necessary guard: never assume stored keys automatically update or stay correct across version drift. Without such a check, an update could produce a new key that conflicts or is different from what the original stored keys were. That scenario would mandate manual review (HOLD) as below.

At this point, all tests pass as expected. The shadow’s data and constraints behave exactly as the canon-policy requires. No unresolved collisions remain in the shadow (they were resolved by renames). No schema enforcement conflict remains (the function is present and deterministic). All known behaviors have been explicitly tested.

Prevent Unicode and function drift from changing the policy silently

Even with successful tests above, we must acknowledge a risk: the system’s notion of "Unicode normalization" or "case folding" may drift if any component changes. Python’s unicodedata is based on a specific Unicode version (15.1.0 here). Suppose in the future the code is run with Unicode 16.0.0 tables; then canon_name("Straße") might (hypothetically) produce something different or break old keys. Similarly, if someone edits the function’s code (e.g. removing NFC or using NFKC), that changes the contract. SQLite’s label "deterministic" does not shield against this; it only means “given same arguments, it will return same result during one process run.”

Thus, we should store a version tag inside the database, e.g.:

PRAGMA user_version = 1;  -- or create a meta table with 'policy=1.0, unicode=15.1'

Before each write, the application should verify the current function’s version digest vs the stored value. If mismatch, the write should fail (HOLD situation). This way, if an admin upgrades to a new Unicode standard or alters the function, they must review the migration. We did not automate this in our test, but we mention it to underline: policy drift is a form of collision. We will consider a mismatch as an unresolved drift.

In our scenario, with no drift, shadow enforcement stands. But a mismatch would have forced us to HOLD and coordinate before any new writes (see decision matrix).

Database systems are insensitive to Unicode versions; they rely on what the application provides. As such, version-control of normalization logic is crucial for consistency. For more on schema-version bindings, see best practices in SQL query correctness foundations and Python’s sqlite3 documentation.

Choose accept, refactor, hold or rollback

We now compile the final decision based on our evidence. The options are:

• ACCEPT the uniqueness contract (shadow schema) as valid, to move toward applying it in production.

• REFACTOR something if needed (schema change, code update) before acceptance.

• HOLD if unresolved issues remain (collisions or drift) or if the environment is incompatible.

• ROLL BACK the uncommitted shadow migration if we abandon it.

Our evidence:

1. The shadow schema enforces the declared policy correctly (as tested). No collisions remain in it.

2. The original table had collisions that we manually resolved (the rename manifest).

3. The shadow load failure was handled by rollback; we haven’t committed any problematic state.

4. The application writer can’t write new entries without the function, but we ensure it is registered with the correct version.

Given this, ACCEPT the shadow contract: we have an explicit uniqueness schema (the shadow) that exactly enforces the intended NFC+casefold policy without data loss. We should not accept SQLite’s old NOCASE as enforcing our policy, but we have a new one (the generated unique key) that does. However, Refactor is a footnote to acceptance: the schema as-is might not be immediately ready to replace production.

For example, we might need to drop the old index, ensure the app’s writes go to the shadow (or rename tables), etc. But those steps (swapping tables, updating code paths) are beyond this audit. We accept the contract that “shadow.id,shadow.raw_name,shadow.name_key behaves correctly.” The actual migration (“swapping shadow to become projects”) will be a separate planned operation, possibly after downtime or in a new version, with full backup/rollback planning.

We do not recommend rolling back (ROLL BACK) anything here, because no changes were made to the original data. The shadow was just for testing. The unapproved shadow load was already rolled back. If the evidence had shown an unfixable problem, we would recommend not applying this plan. But since it succeeded (with our manual adjustments), we proceed.

Are any unresolved collisions left? No, after applying renames, each group now has distinct keys. Are any runtime issues outstanding? The only one is the requirement that writers have the canon_name function; if that can’t be met, we would put HOLD on implementation until a solution (like a binary extension or ICU) is found. But assuming the team allows a UDF or compiles SQLite with ICU, that’s a policy choice. If not allowed, it’s a hold: “shadow cannot be applied as-is; consult security and dev teams.”

If trusted_schema=ON, the shadow creation would have failed, meaning we’d have to hold. In our lab it passed, but in production that’s an outstanding check.

Our matrix:

• Accept: The shadow schema and data are correct. All IDs preserved; all canonical keys as designed; no duplicates remain in shadow. No error in allowed writes. We accept the contract that name_key = canon_name(raw_name) is now a valid unique constraint. The actual rollout of this change (renaming tables/views in prod, stopping old writers, etc.) is outside this acceptance; that is a migration project of its own. We do not automatically replace the live table. We accept that the schema can enforce it moving forward.

• Refactor: If future requirements change (e.g. we later decide to apply NFKC for compatibility), the schema would need altering (e.g. a new function or column). We flag that any such changes require a new audit. Also, we note that we used COLLATE BINARY; if a future version of SQLite or app relies on some collation logic, we might need to adjust. For now, no refactor needed.

• Hold: We would hold if any of the following were true: any collision group still unresolved (we resolved all), the trusted_schema or UDF issue (see above), or if the policy/Unicode version has changed. Also, if production had other rows (not in our test) colliding, we’d need to include them. Assuming those are accounted for, no holds remain. If we had attempted to insert into shadow and found a diff between stored keys and expected (e.g. hex mismatches), we would hold. No holds at present, pending signoff on UDF deployment.

• Roll Back: This refers only to the shadow migration attempt, which we did. We rolled back the failed batch, and the manual batch was done in a new transaction. There's no committed shadow data to roll back in production. So the roll-back action was already done for the partial test. No need to rollback anything else.

Thus, our verdict: Accept the tested uniqueness contract, with the caveat that deploying it to production (swapping tables, adding function to prod system) is a separate planned rollout. Because we applied explicit renames, we did not lose data. The new unique key column, as demonstrated, will enforce the same policy on every writer that has the canon_name UDF loaded.

We document in our decision:

1. Accept the shadow (ID and name_key mapping).

2. Discard the old unsynchronized NOCASE constraint in favor of this approach.

3. Plan a proper migration (not detailed here) to replace the original schema with this one.

4. The unresolved collisions {3, 4}, {5, 6}, {7, 8} were temporary and have now become [3, 4'], [5, 6'], [7, 8'] where one partner got a "-b" suffix. If the team later decides to give them final names (instead of "-b"), that is out-of-band. The important point: each original row is accounted for with a unique name.

The decision matrix summarizes the available actions:

We have: first row “Accept” applicable, no other conditions apply.

Finally, we explicitly note: “Accept” means accepting the shadow table as the new canonical design, not that any change has gone live yet. We acknowledge that an actual swap would need a deployment plan with backup, checks, and downtime for cutting over the unique constraint.

Refonte’s guidance on database migration automation provides related deployment context. Here, we validate in a shadow copy before considering a controlled swap. We accept only the shadow contract; we do not perform the actual swap.

Define the boundary of a separately approved migration

This audit was done by (hypothetically) the schema owner or a senior database engineer with authority to define policies and design constraints. The scope was this one table and its writer. In a broader system, the owners include:

1. The namespace owner (who declared canonization rules, perhaps the product or ID team).

2. The schema owner (DBA or arch with rights to ALTER tables).

3. The writer maintainer (dev team in charge of code that writes project tags).

Each has a role:

1. The namespace owner sets the rule and approved renames.

2. The schema owner (us) ensures the database side enforces it.

3. The writer maintainer must incorporate the UDF and enforce prechecks (version, etc.).

We must explicitly warn: this isolated table test omits many dependencies. For example:

1. If there were foreign keys referencing this table, they need maintenance. (We had none in the lab.)

2. If other tables or views join on projects.raw_name, they may need updating once names change.

3. If multiple writers write this table concurrently, we should consider whether there are race conditions (here we used single-writer transactions). A full migration would need to stop writes, run a safe DDL, and restart.

4. Backup and restore procedures must be updated to include the function and any code changes.

None of these are handled in this local lab. We only focused on the table’s own constraint. In production, a separate migration plan should:

• Stop writes or duplicate writes: Ensure no one is writing new project tags during the change.

• Backup data.

• Possibly rename the old table (e.g. projects_old), rename shadow to projects, and copy any needed indexes or triggers.

• Update any code/libraries to use canon_name on inserts (as a pre-insert check).

• Test referential integrity if those names appear as references elsewhere.

• If something goes wrong, have a rollback plan (restore from backup).

That is beyond our testing. We noted these steps here to contextualize: accepting the shadow is just the first half of a migration; the actual swap and integration require a full outage plan.

This issue (literal names vs stored keys) lives at the API–database boundary. App code must honor the same rules to produce valid data for the new schema. We assume the application has been updated to call canon_name before inserting; if not, writing directly without it would fail. This boundary (the “stored canonical key policy”) must be clearly documented and versioned across both the database and app code. In our lab, only our Python script implemented it, but in a real system the API or ORM layer needs the same logic or else disallow raw writes.

In short, our scenario fixed the table’s side of the contract. But the table sits in a larger ecosystem: other tables, code, and processes will need review. For example, if another table looked up a project by raw name, we should ensure it does not rely on comparing old and new values incorrectly. None of those were in scope. Anyone implementing this change should check all touchpoints.

These concepts distinguish canonical key enforcement in the DB from key generation in the app.

Build database-integrity skills with Refonte Learning

Ensuring data integrity, especially with text and internationalization, is a sophisticated challenge. This exercise, auditing a uniqueness constraint against a precise Unicode policy, is the kind of advanced problem that database administrators and backend engineers must handle. If this article piqued your interest, consider deepening your skills: explore Refonte Learning’s Database Administrator Essentials program. It covers fundamentals like schema design, transaction integrity, uniqueness and foreign keys, migrations and rollbacks, and more. While it doesn’t promise specific coverage of every library like sqlite3, it teaches the underlying principles and practices (3 months, 12–14 hrs/week) that make one confident in designing and evolving databases. Explore Refonte Learning's Database Administrator Essentials program to build broader knowledge of database design, migration planning, and integrity-focused administration.

By working through cases like this (and others such as WAL backups, referential integrity audits, performance tuning, and security practices), you’ll be better prepared to tackle the nuanced scenarios modern applications present. Remember, handling Unicode and casefolding correctly is part of quality data management, not magic. Practice, thorough testing, and a solid grasp of collation rules (both in your DBMS and your app language) are key. The essentials program can help hone those skills and give you the framework to apply them in any database environment.