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

Anthony · Accepted Answer · 2012-06-26T03:36:03.687

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.

I have a table where I would like to do validation on two other fields. sh_code and sh_organization. I tried with your suggested code but record is still inserted. Any suggestions — Yebach, Jan 28 '15 at 12:55
Hard to say without seeing your code. The above approach works with any two fields. — Anthony, Jan 28 '15 at 14:45

score 3 · Answer 2 · edited May 23 '17 at 12:19

3

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.

edited May 23 '17 at 12:19

Community

1
1

answered Jan 22 '13 at 21:18

crayzeewulf

5,840
1
27
30

This is actually a clever approach; however, I've decided to use the built in input validation to keep things standard. – C Fairweather Dec 15 '14 at 17:49
2

This is _almost_ correct. You should also take into account the cases where the different combinations of `user` and `uid` result in the same string, e.g. `user=1` and `uid=21` vs. `user=12` and `uid=1`. In this case both will yield the `md5('121')` and will conflict with each other. – Chen Levy Jan 03 '16 at 09:06
@ChenLevy, good point. To account for this possibility I have updated the solution slightly. Let me know if you see any issue with it. – crayzeewulf Feb 22 '16 at 16:35
Actually I think it would make more sense to simply use integer: `lambda row: ((row.user << 16) | (row.uid))`. I might be wrong but there's always a chance you get same md5 for 2 different pairs of user/uid. That's assuming each ID field is smaller than 16. – bcelary Apr 22 '16 at 20:09

composite key in web2py

2 Answers2

Linked