Bash-Shell.Net

My previous post about extending Django's model validation led to a new requirement, partial unique indexes. The extended validation works, but there's still a race condition between validation and saving, so you can validate that the current values don't violate the unique constraint and then another object can come in and get saved before your current object. To keep this from causing a problem, the uniqueness needs to also be enforced at the database level.

Fortunately, Postgresql has partial indexes and partial unique indexes, specifically, which solve this problem if you're using postgres. Unfortunately, Django does not support these directly. There's a very simple solution to having these created when the table for your model is created, but it seems very few people think of it, so I want to mention it here. The simple solution is Django's ability to use SQL commands in a file.

You can create a sql directory within your app and then create sql files named after your model. So if your model is MyModel, then you create mymodel.sql. You can even have database backend specific files by including the database driver name. Partial indexes are a good candidate for this since MySQL, SQLite, and Oracle don't support them (although I believe a similar end result can be accomplished with Oracle), so you would create the file mymodel.postgresql_psycopg2.sql if you were using the postgresql_psycopg2 driver. Then the partial index will get created if you're using postgresql_psycopg2, but not any of the databases that don't support it.