Working on a project I had following problem.

I had to set isUnique validation of a field. The field has VARCHAR(10) type. When I save the data the “isUnique” validation doesn’t work as expected while I was sure that the there is such record in the database. Digging through the problem it turn out that the problem is in the length of the DB column.

Let me explain. The field was length 10, but in the form I’ve put the value which is 11 characters long, so when the first data is submitted instead of the 11 characters, only 10 are saved in the DB table. My value was 456-23-2010 while in the db it’s saved 456-23-201.

So when inserting the new data the script checks 456-23-2010 against the database where is stored 456-23-201. and of course it passes the validation. While on the edit action strangely it appear that the value it is not unique any more (because there are already 2 records 456-23-201 in the db. 🙂

A little bit tricky, because at first glance the data is almost the same. For sure if the inserted string was large than the saved in the DB I would spot it easily, but to me the number was really the same.

There are 2 possible ways where I would think how to solve this problem in the future: Either modifying the Bake template for my applications, or using better validation technique – where the value is trimmed up to length of the field.

I was wondering what would be the better way?