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?

Egg

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.

Mikiso

I was hoping for something that would actually create a constraint enforced by the database. I will try out the requires... but I don't really like having to make sure all data is persisted through forms. – Egg

The web2py DAL syntax doesn't provide that option, but you can always use db.executesql(...) to execute the relevant SQL directly. – Mikiso

Answer 2

You could also try using the Before and After callbacks. Using Anthony's table as an example, you would do something like:

db.rates._before_insert.append( lambda r : db( (db.rates.from_currency==r["from_currency"]) & (db.rates.to_currency==r["to_currency"]) ).select() )

If the .select() query returns anything but None or False, it will abort the current db.rates.insert() and give False as a return . Notice that it still won't create an UNIQUE CONSTRAINT, but its way more safe than using some client-side validation.

Marinelli