1

The question of composite keys in web2py was answered pretty well by Anthony here, but I'm looking for an answer to a caveat he included in his answer. His answer suggested using primarykey and IS_NOT_IN_DB in form validation, and the caveat (and further suggestion) was:

...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 added the CREATE UNIQUE INDEX for the fields. My main question is, what's the best way to catch the exceptions that will get thrown for these errors? Is there a single (or a few) place(s) where I can catch the exception the db layer will throw when I try to INSERT a record (with a duplicate key) outside of a user form (which would bypass the IS_NOT_IN_DB check/technique)? Or do I have to make sure the team knows that when they do any INSERTs on this table they have to be prepared to catch that exception? Or, ... ? Also, if I have two users trying to insert such duplicates, is there an easy way in SQLFORM for me to catch the exception that will get thrown? (Sorry for all the newbie questions.)

Community
  • 1
  • 1
MichaelF
  • 149
  • 1
  • 8

1 Answers1

1

Is there a single (or a few) place(s) where I can catch the exception the db layer will throw when I try to INSERT a record (with a duplicate key)?

I don't think so, but you could write a custom function to wrap such inserts in a try...except (or otherwise check for duplicate keys).

Also, if I have two users trying to insert such duplicates, is there an easy way in SQLFORM for me to catch the exception that will get thrown?

When using SQLFORM, the validator should catch the duplicate submission attempt and return an error on the form (for the second user).

Anthony
  • 25,466
  • 3
  • 28
  • 57
  • what if I want to prevent inserting a duplicate record (eg. field name (not table Id)) into db for one user but another can insert it with the same name – Yebach Jan 28 '15 at 12:24
  • Are you saying some users are allowed to submit duplicates and others are not? If so, you could make the validator conditional on the user (via whatever method you use to identify users). – Anthony Jan 28 '15 at 14:47
  • No. One user can not create duplicate values on code sh_code. but there can be two records with same values in sh_code field. Another identifirer field is sh_user. – Yebach Jan 28 '15 at 14:53