Unique constraint is not honoured when specified with IS_IN_DB in web2py

I defined a table as follows:

db.define_table('special_users',
    Field('sp_user', db.auth_user, required=True, notnull=True, label='Special User', represent=lambda lead,row: lead.first_name + ' ' + lead.last_name, unique=True),
    format=lambda row: (row.lead.first_name + ' ' + row.lead.last_name) or 'N/A')

And I specified the requires constraint as follows

db.special_users.lead.requires = IS_IN_DB(db, db.auth_user, '%(first_name)s %(last_name)s', zero=None)

Now, when I generate a form for the table using SQLFORM(db.special_users) it allows me to add a user as many times as I want, which is a violation of unique=True constraint. How can I get this rectified?

Solution

For some reason, I think maybe notnull and unique constraints don't get applied to reference fields. In any case, if inserts are generally made via forms, you are better off using an IS_NOT_IN_DB() validator on the field, as that will enable proper error handling and display when processing the form. Relying only on unique=True would result in an exception upon a duplicate insert attempt, which would generate a non-specific 500 server error for the end user.