WebTek: Unique constraints and the alreadyexists error

The only way to enforce unique constraints on a database table is by asking the database to do it. Create the table, create the unique constraint, do an insert, and parse the error message if the insert failed, to see if it was a unique constraint violation. Any kind of SELECT COUNT(*) before an insert will not work.

This is now available automatically from WebTek.

If the underlying table has any unique constraints, if they are violated, you want to let the user know of this. Unfortunately databases provide unhelpful constraint violation messages such as:

ERROR 1062 (23000): Duplicate entry 'abc' for key 2

In case of multiple constraints on the table, the only way to know which one has been violated is the "key 2" part of the message. The front-end needs to know which field should be highlighted with the error, so the UNIQUE_CONSTRAINTS method in a Model should be defined in your model having a relationship from the "key" in the error message to the column which should be highlighted as having the error in the front-end. For example:

sub UNIQUE_CONSTRAINTS { { 2 => 'name' } }

In the case that the above error is received, an error will be displayed on the "name" field. If the model being defined is MyModel then the following message key will be searched for:

Model.MyModel.name.alreadyexists

(Originally posted to the WebTek blog; WebTek is a Perl web framework)