CODEWITHSUNDEEP
mysqldatabase
Akashdeep Saluja
Akashdeep Saluja

Reputation: 3089

Maintaining constraints on input in MYSQL

I have a list of inputs, where each input can be having restriction on it, such as it should be numeric with max 15 digits, or alphanumeric of max 20 characters.

I am not sure how to store it in database, or should I hardcode for each input its constraints in the code which doesn't feel right to me.

Upvotes: 0

Views: 33

Answers (1)

Gustavo Rubio
Gustavo Rubio

Reputation: 10827

Constraints on a database are a different thing than validations. I think you are looking really to validate input from your application. For instance, you mention a field that should be numeric with max of 15 digits, and, while you can create a trigger to validate such things its just not practical, to store a 15 digits number in mysql (with no decimals) you would need a BIGINT which allows numbers up to 9223372036854775807 which is more than 15 digits. There is no easy way to just setup the schema for that kind of thing.

Other cases, like the one for alphanumeric with 20 chars maximum can be accomplished though, using a VARCHAR(20) for instance, but it is still impractical to rely on the database for that.

Best bet is to validate your input at the UI level and maybe as an added security step, check on your database models or wherever you do the inserts/updates.

One good example for why doing constraints at the database schema and specific validations on the code is when your application business rules change in a way that you need to be flexible in the input, say that eventually you need to allow for specific scenarios where codes can be either 10 or 15 digits, you still want to allow the database to store numbers of both lengths but you would validate either case at your application validations.

Upvotes: 1

Related Questions