Multi-Column Unique Constraint with web2py

Question

It is possible to mark a particular column as unique=true. What is the most correct way to handle multi-column unique constraints in web2py?

For example, say I have an exchange rate table. It could have columns from-currency, to-currency and the exchange rate. It would not make sense to have two rows with the same from and to currencies. What would be the most elegant or correct way to make the from/to combination unique?

Answer 1

Assuming the data will be entered via a form, you could use a form validator, like this:

db.define_table('rates',
    Field('from_currency'),
    Field('to_currency'))

db.rates.to_currency.requires=IS_NOT_IN_DB(
    db(db.rates.from_currency==request.vars.from_currency), 'rates.to_currency')

That will make sure to_currency is unique among the set of records where from_currency matches the new value of from_currency being inserted (so the combination of from_currency and to_currency must be unique).

Another option is to use an onvalidation function to confirm the two values are distinct -- this will run after the usual form validation, but before the DB insert.

Finally, you could instead do the validation client-side via Javascript.