This article explains why ordinary unique constraints fail with soft‑deleted rows, and shows how to maintain email uniqueness by using partial unique indexes in PostgreSQL or generated‑column‑based unique indexes in MySQL/MariaDB, including multi‑tenant adaptations.
This article explains SQLite's Rollback and WAL logging modes, compares their performance and concurrency characteristics, investigates a real‑world problem where duplicate IM message IDs appear after app upgrades, and presents a step‑by‑step solution using UNIQUE constraints and index checks to prevent repeated inserts.
The article discusses efficient ways to perform bulk inserts in MySQL while avoiding duplicate records, covering techniques such as INSERT IGNORE, ON DUPLICATE KEY UPDATE, INSERT…SELECT…WHERE NOT EXISTS, REPLACE INTO, and demonstrates a practical MyBatis implementation with a unique mobile_number constraint.
The article reproduces a MongoDB issue where queries using a hashed shard key return no results and a compound unique index silently accepts duplicate documents, explains that the failIndexKeyTooLong parameter allows oversized index entries to be stored without indexing, and shows how to reproduce and fix the problem.
This article examines common scenarios where duplicate rows prevent creating unique constraints, presents eight SQL‑based methods—including array functions, window functions, NOT IN/EXISTS, combined IN/NOT IN, EXISTS/NOT EXISTS, single‑statement deletes, and table‑copy approaches—provides test data, performance comparisons, and practical guidance for DBA operations.
