web2py unique constraint not being enforced

Question

I am new to web2py. I am making an application for document registration. Each document is assigned a number, unique for the year of registration. I am trying to have a computed string field, combining the number and year, that enforces the unique=True constraint.

I am using SQLite, the web2py default database. My table definition:

db.define_table('doc_master_new',
            Field('sr_no', 'string', unique=True, notnull=True,
                  compute=lambda r: str(r['inward_no']) + '/' + str(r['inward_date'].year)),
            Field('inward_no', 'integer'),
            Field('inward_date', 'date', default=request.now),
            Field('doc_date', 'date'),
            Field('doc_type', db.doc_type, requires=IS_IN_DB(db, db.doc_type, '%(type_code)s', orderby=db.doc_type.id)),
            Field('applicant_type'),  ## creditor/borrower/third-party
            Field('no_defect', 'boolean', default=False),
            Field('time_stamp', 'datetime', default=request.now)
           )

and the controller:

def add_doc():    
db.doc_master_new.sr_no.writable = False
db.doc_master_new.sr_no.readable = False
db.doc_master_new.time_stamp.writable = False
db.doc_master_new.time_stamp.readable = False
db.doc_master_new.no_defect.writable = False
db.doc_master_new.no_defect.readable = False
form = SQLFORM(db.doc_master_new,
              labels = { 'inward_no':'SR No',
                        'inward_date':'SR Date',
                        'doc_date':'Document Date',
                        'doc_type':'Document Type',
                       }
              )
if form.process().accepted:
    session.flash = 'Document Added'
    redirect(URL('index_n'))
return locals()

The unique constraint is not being enforced, and identical values are being inserted into the table. I cannot understand why. SQLite documentation says NULL values are considered distinct from all other values, including other NULLs, and so added a notnull constraint, but still duplicates are being allowed.

Can someone please help?

Answer 1

Most likely you added unique=True after the table was initially created. Although they DAL handles migrations, it will not make changes to constraints such as UNIQUE and NOT NULL -- if you want to change those constraints after the table has already been created, you must do some via some other tool.

Also, keep in mind that unique=True is enforced at the level of the database, not the form input. That means if a user submits values that violate the constraint, the database driver will throw an exception and the user will see an error ticket. Instead, consider enforcing the constraint at the time of form submission, possibly via the onvalidation callback of .process(). That way, you can prevent duplicate combinations from being sent to the database and report back a friendly error message to the user.

Finally, if the only purpose of the sr_no field is to enforce the uniqueness constraint, consider instead setting up a multi-column uniqueness constraint (you will have to do that outside of the DAL, which does not provide a mechanism for defining such constraints).