prize18-8  Exercise 2: Two Ways to Enforce Unique Names
=======================================================

Predictions: NOCASE folds only ASCII letters, so it rejects "ZOE" (same as "Zoe") but accepts "Zoë" (new), accepts "ZOË" (É is not folded to ë),
accepts the decomposed Zoë (different code points) and accepts the fullwidth spelling. The key column (NFKC then casefold) turns the composed, upper-case,
decomposed and fullwidth forms of Zoë into one key, so after "Zoë" is added all later variants are rejected.

ex2.py:

  import sqlite3
  import unicodedata as ud

  def key(name):
      return ud.normalize("NFKC", name).casefold()

  con = sqlite3.connect(":memory:")
  con.execute("CREATE TABLE a (name TEXT UNIQUE COLLATE NOCASE)")
  con.execute("CREATE TABLE b (name TEXT, name_key TEXT UNIQUE)")

  def add_a(n):
      try:
          con.execute("INSERT INTO a VALUES (?)", (n,))
          return "ok"
      except sqlite3.IntegrityError:
          return "duplicate"

  def add_b(n):
      try:
          con.execute("INSERT INTO b VALUES (?, ?)", (n, key(n)))
          return "ok"
      except sqlite3.IntegrityError:
          return "duplicate"

  for n in ["Zoe", "ZOE", "Zo\u00eb", "ZO\u00cb", "Zoe\u0308", "\uff3a\uff4f\u00eb"]:
      print(f"{ascii(n):22} NOCASE column: {add_a(n):10} key column: {add_b(n)}")

Real output (Python 3.14, bundled SQLite):

  'Zoe'                  NOCASE column: ok         key column: ok
  'ZOE'                  NOCASE column: duplicate  key column: duplicate
  'Zo\xeb'               NOCASE column: ok         key column: ok
  'ZO\xcb'               NOCASE column: ok         key column: duplicate
  'Zoe\u0308'            NOCASE column: ok         key column: duplicate
  '\uff3a\uff4f\xeb'     NOCASE column: ok         key column: duplicate

Only the ASCII case duplicate is caught by NOCASE. The key column also catches the upper-case accented, decomposed and fullwidth variants.
Note that "Zoe" and "Zoë" stay distinct in both tables: normalization and case folding do not remove accents (that is a separate policy decision).