4 ms·
Modern DB engines can enforce Unique or PK constraints without indexes. Yes, they perform scans.
by ComodoHacker 3y ago
Modern DB engines can enforce Unique or PK constraints without indexes. Yes, they perform scans.
- tomnipotent 3y agoEvery major RDBMS requires an index for both constraints.
- hinkley 3y agoFK constraints however are a pretty common gotcha. You have a table that allows deletes, and every table that has a column pointing to that table gets scanned on each delete operation. So you have to add an index on that column, or start talking about tombstoning data instead of deleting it, but in which case you may still need the FK index to search out references to an old row to replace with a new one.
- tomnipotent 3y agoAn FK also adds the burden of keeping a copy of each related row during the lifespan of a transaction. This means small-but-frequently-updated tables that are joined to a lot can be a source of unexpected headaches.
- ComodoHacker 3y agoAfter checking the docs, you are right, I stand corrected. I was pretty sure Oracle and DB2 don't.