SQL & databases

Query correctness, transactions, data types and database connections.

60 troubleshooting guides · Page 3 / 3

49. Why can INSERT OR REPLACE delete related data in SQLite?

    REPLACE is not simply an in-place update. On certain constraint conflicts it deletes the conflicting row before inserting the replacement, which can interact with foreign keys and triggers.

50. Is SQLite AUTOINCREMENT required for every integer primary key?

    An INTEGER PRIMARY KEY already has special rowid behavior in ordinary rowid tables. AUTOINCREMENT adds a narrower non-reuse guarantee with additional bookkeeping.

51. Can I safely copy only the main SQLite file while the app is running?

    A live database can have committed state in journal or WAL files, and a raw copy may not represent one consistent point in time.

52. When does BEGIN IMMEDIATE help with SQLite write contention?

    A deferred transaction can read first and later fail while trying to upgrade to a writer. BEGIN IMMEDIATE attempts to obtain the write reservation at transaction start.

53. Why does SQLite LIKE behave differently for accented letters?

    Default case-insensitive LIKE behavior is not a full Unicode case-folding solution. ASCII expectations do not automatically extend to every alphabet.

54. Why does full-text search not match an arbitrary substring?

    Full-text indexes usually operate on tokens and language processing, not every possible character substring. Tokenization can discard or normalize input.

55. Can a SQL placeholder safely select a table name?

    Value placeholders represent data values, not SQL identifiers or keywords. Binding a table name does not turn it into a table reference.

56. How do I detect an N+1 query problem in a list endpoint?

    An endpoint can run one list query and then one extra query per row for related data. It works on small fixtures but scales with the number of results.

57. How can a unique email constraint work with soft-deleted accounts?

    Soft deletion leaves the row present, so an ordinary unique constraint still reserves its value. The desired reuse policy must be explicit.

58. How do I avoid committing a database change without sending its event?

    A database transaction and an external message send usually do not share one atomic commit. A crash between them creates a consistency gap.

59. Why does a newly saved record disappear when read from a replica?

    An asynchronous read replica can lag behind the primary. A successful commit on the writer does not mean every replica has replayed it.

60. Why can an application start successfully against the wrong database schema version?

    Opening a connection proves connectivity, not compatibility with the tables and columns the application expects. The first affected request may fail much later.

Ask your own question · Sign in to post questions and answers.