web2py prevent duplicates in many to many table

Question

I have a table to manage many to many relationship (workers and skills) Workers can have multiple skills and one skill can be assigned to multiple workers

What would be the best way to prevent duplicate entries so one skill is not assigned to the same worker twice?

thank you

Answer 1

If you have something like:

db.define_table('worker_skill',
    Field('worker', 'reference worker'),
    Field('skill', 'reference skill'))

To prevent duplicates via form submissions, you could then add a validator to one of the fields, such as:

db.worker_skill.skill.requires = IS_NOT_IN_DB(
    db(db.worker_skill.worker == request.vars.worker), 'worker_skill.skill'
)

The above will ensure that the value being inserted in "skill" does not exist among the set of records where the value being inserted in "worker" matches the "worker" field.

Another option for form validation is use of an onvalidation callback, as explained in the forms chapter of the book.

You can also set a unique constraint on the pair of columns directly in the database (web2py does not handle that, so you will have to do that via an external tool). That will not help with form validation, as a violation of the constraint will simply result in the database driver throwing an exception (rather than presenting a friendly error message to the end user), but it will be useful if you are making inserts via means other than a web2py form.