How to enforce a unique index constraint in Python
Mock a database unique index in Python that rejects duplicate rows based on one or more columns.
Python code
38 linesclass MockIndex:
def __init__(self, columns):
self.columns = columns
self._values = set()
def insert(self, row):
key = tuple(row[col] for col in self.columns)
if key in self._values:
raise ValueError(f"Duplicate key {key} for columns {self.columns}")
self._values.add(key)
def delete(self, row):
key = tuple(row[col] for col in self.columns)
if key not in self._values:
raise KeyError(f"Key {key} not found")
self._values.remove(key)
def contains(self, row):
key = tuple(row[col] for col in self.columns)
return key in self._values
if __name__ == "__main__":
index = MockIndex(["email"])
row1 = {"id": 1, "email": "a@b.com", "name": "Alice"}
row2 = {"id": 2, "email": "c@d.com", "name": "Bob"}
row3 = {"id": 3, "email": "a@b.com", "name": "Eve"}
index.insert(row1)
index.insert(row2)
try:
index.insert(row3)
except ValueError as e:
print(f"Error: {e}")
print(f"Contains row2: {index.contains(row2)}")
index.delete(row2)
print(f"Contains row2 after delete: {index.contains(row2)}")
Output
Error: Duplicate key ('a@b.com',) for columns ['email']
Contains row2: True
Contains row2 after delete: False
How it works
The MockIndex class stores only the tuple of column values for each inserted row in a set. set membership is O(1) average, so lookups and duplicate checks are fast. insert builds the key from the specified columns and raises ValueError if the key already exists. delete removes a key and raises KeyError if the row was not found. This simulates the behavior of a unique index without needing an actual database, making it easy to test your logic locally.
Common mistakes
- Forgetting to order the columns consistently, causing keys to look different for the same data
- Not using a tuple for the key, which causes unhashable type errors for mutable containers like lists
- Assuming `set` preserves insertion order, which is not guaranteed
- Not handling the case where a row dict may not contain all indexed columns
Variations
- Use a `defaultdict(set)` to store multiple keys if you later need a non-unique index
- Swap the set for a SQLite in-memory table with a UNIQUE constraint for a more realistic test
Real-world use cases
- Testing unique email or username validation before inserting into a real user table.
- Simulating a unique composite index for an order with order_id + product_id during integration tests.
- Validating uniqueness in an in-memory cache layer before committing to a database write.
Sponsored
More from Database scaling & optimization
- Approximate Count with HyperLogLog in Python medium
- B-Tree Insert and In-Order Traversal in Python hard
- Broadcast a Small Reference Table in Python easy
- Build a Full Text Search Index in Python medium
- Build a Partial Index Mock in Python for Database Filtering easy
- Composite index leftmost prefix in Python medium
Keep learning
Related tutorials and quizzes for this topic.