A few bits of advice that spring to mind:
- It seems to me that your primary key is (user, snake_name).
- Don’t constrain snake names to be globally unique, only unique within a particular user. You don’t want users to be able to discover each others’ data by trying names and seeing what’s already taken.
- There are lots of ways of introducing new constraints while keeping the old data. For example, you could store a ‘schema version’ number in the table, and not count records with old schema versions when checking for duplicates. Or start a new table for the new, unduplicated data. Or have a schema version column in the Users table so legacy users have the old constraints applied.
- If I were you, I would compare snake names case-insensitively (
snake1.lower() == snake2.lower()) but store them with the case information. Users would probably like to choose where to put the capital letters!