This is expected and follows from the documented rules. See https://sqlite.org/datatype3.html section 4.2: "If one operand has INTEGER, REAL or NUMERIC affinity and the other operand has TEXT or BLOB or no affinity then NUMERIC affinity is applied to other operand." so the condition "a (text affinity) = b (integer affinity)" is equivalent to the condition "cast(a as integer) = b" for values of a that "look like" a number. This precludes using the unique index on a. BTW: The condition "a = +b" also works, as "+b" has NO affinity and thus gets TEXT affinity applied.