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.

Easy Python 3.9+ Aug 9, 2026 Database scaling & optimization 14 views 0 copies

Python code

38 lines
Python 3.9+
class 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

stdout
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

  1. Use a `defaultdict(set)` to store multiple keys if you later need a non-unique index
  2. 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

Run this sample

Open the browser IDE to tweak the example and see results without installing anything.

Open editor

More from Database scaling & optimization

Related tutorials and quizzes for this topic.