composite key in web2py

Question

I have a table defined in web2py

db.define_table(
'pairing',
Field('user',writable=True,readable=True),
Field('uid', writable=True , readable=True)
)

This table needs to have user and uid combination being unique. I have looked through the web2py documentation , but there isn't direct way to define composite key . How do we define composite way in web2py

Answer 1

It depends on what you are trying to do. By default, web2py automatically creates an auto-incrementing id field to serve as the primary key for each table, and that is the recommended approach whenever possible. If you are dealing with a legacy database with composite primary keys and cannot change the schema, you can specify a primarykey attribute, though with some limitations (as explained here):

db.define_table('pairing', 
    Field('user', writable=True, readable=True), 
    Field('uid', writable=True, readable=True),
    primarykey=['user', 'uid'])

Perhaps instead you don't really need a true composite primary key, but you just need some way to ensure only unique pairs of user/uid values are inserted in the table. In that case, you can do so by specifying a properly constructed IS_NOT_IN_DB validator for one of the two fields:

db.define_table('pairing',
    Field('user', writable=True, readable=True),
    Field('uid', writable=True, readable=True))

db.pairing.uid.requires=IS_NOT_IN_DB(db(db.pairing.user==request.vars.user),
    'pairing.uid')

That will make sure uid is unique among the set of records where user matches the new value of user being inserted (so the combination of user and uid must be unique). Note, validators (such as IS_NOT_IN_DB) are only applied when values are being inserted via a SQLFORM or using the .validate_and_insert() method, so the above won't work for arbitrary inserts into the table but is primarily intended for user input submissions.

You can also use SQL to set a multi-column unique constraint on the table (which you can do directly in the database or via the web2py .executesql() method). Even with such a constraint, though, you would still want to do some input validation within your application to avoid errors from the database.

Answer 2

I have been using a computed field to create/simulate a composite key. Taking the example from the above question, one can define the junction table as follows:

from md5 import md5
db.define_table( 'pairing',
                 Field('user', writable=True, readable=True),
                 Field('uid', writable=True, readable=True),
                 Field( 'user_uid_md5', 
                        length=32,
                        unique=True,
                        writable=False,
                        readable=False,
                        compute=lambda row: md5("{0}:{1}".format(row.user,row.uid)).hexdigest()))

The user_uid_md5 field is automatically computed on insert and updates. The value of this field is the md5 hash of a string obtained from the two fields user and uid. This field is also marked as unique. So the database enforces uniqueness here and this works around the limitation pointed out by Anthony. This should also work to emulate composite keys with more than two fields. If you see any holes in this approach, please let me know.

Edit: Slight update to the way the md5 hash is computed to account for the case pointed out by Chen Levy in a comment below.