I am using SQLAlchemy and PostgreSQL and I would like to enforce a unique constraint on two columns in one table in sqlalchemy. One column is numeric (Integer), and one is alphanumeric string. The alphanumeric column must be stored 'as is' (i.e. a mix of upper and lower case chars), but the unique constraint being enforced needs to be case insensitive.
It is a list of word 'tags' each with a foreign key to 'topics'. Consider three columns in the tags table: id, topic_id, and name. There cannot be more than one of the same tag name (on a case insensitive basis) for each topic. In other words, if the topic 'fish' (tags.topic_id = 7) already has a tags.name of 'goldfish', you would not be able to add another row with tags.topic_id=7 and with a tag.name of 'GolDFiSH'. The two tags are identical and the topic_id is the same on a case-insensitive basis.
What is the right way to do this?
I have come across two relevant references for thought starters:
(i) Defining a new variable type: https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/StringComparisonFilter
(ii) Raw SQL: Create Unqiue case-insensitive constraint on two varchar fields
Option (i) and (ii) both seem ok, but which of these two is better, or is there a third better way?
The simplest solution is what I am after.
My current idea is to create another 'phantom' column called, say, tags.lower_case.name, which gets populated with a lowercase version of tags.name as a consequence of a custom init method and a custom setter for tags.name. Then enforce the unique constraint on this phantom column.
Another idea I am considering is to to see if I can create an index across the two columns, and enforce some unique constraint on that index (which ignores case on one column), see here.
