CODEWITHSUNDEEP
pythonsqlalchemyconstraints
Joril
Joril

Reputation: 20547

Compound UniqueConstraint with a function

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!

Upvotes: 3

Views: 1028

Answers (2)

Ants Aasma
Ants Aasma

Reputation: 54882

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__)

This method works very nicely but throws a SADeprecation warning in v0.7 The syntax that I've used successfully:

from sqlalchemy import event

event.listen(ModelObject.__table__,
         'after_create',
          DDL("CREATE UNIQUE INDEX term_year ON %(fullname)s "
              "(EXTRACT(YEAR FROM start_date), term)",
              on = 'postgresql'
              )
         )

Upvotes: 4

fucx
fucx

Reputation: 141

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.

Upvotes: -1

Related Questions