Compound UniqueConstraint with a function

Question

Hi everyone!
A quick SQLAlchemy question...

I have a class "Document" with attributes "Number" and "Date". I need to ensure that there's no duplicated number for the same year, is there a way to have a UniqueConstraint on "Number + year(Date)"? Should I use a unique Index instead? How would I declare the functional part?

(SQLAlchemy 0.5.5, PostgreSQL 8.3.4)

Thanks in advance!

Answer 1

I'm pretty sure that unique constraints can only be applied on columns that already have data in them, and not on runtime-calculated expressions. Hence, you would need to create an extra column which contains the year part of your date, over which you could create a unique constraint together with number. To best use this approach, maybe you should store your date split up in three separate columns containing the day, month and year part. This could be done using default constraints in the table definition.

Answer 2

You should use a functional unique index to apply this constraint. Unfortunately the database generic database independent schema definition machinery in SQLAlchemy doesn't abstract functional indexes yet. You'll have to use the DDL construct to register custom schema definition clauses. If you are using the declarative approach to declaring your schema add the following after your class definition:

DDL(
    "CREATE UNIQUE INDEX doc_year_num_uniq ON %(fullname)s "
    "(EXTRACT(YEAR FROM date), number)"
).execute_at('after-create', Document.__table__)