Fix length number in mysql

Question

Hi all,

I want to have a field in a Mysql table, which should accept inputs having a fixed size - no more, no less. The input data is a number, but solutions for strings can also be considered, as I have no problem storing this data as varchar like stuff.

To be exact, I want a datatype which will NOT allow me to store a number which is having less than 7 or greater than 7 digits. I dont want to use triggers/stored procedures.

Answer 1

This may be possible with a stored procedure, but I wouldn't do this on database level. Validation like this belongs in your application.

Answer 2

I don't believe there is any way to achieve this in MySQL at present without using triggers or stored procedures. If MySQL supported check constraints then you could do it, but it doesn't, so you can't.

Answer 3

The possible solutions are:

• TRIGGER on update/insert.
• CHECK constraint, but MySQL parses and promptly discards check constraints.
• Application-level validation.
• Foreign key to a lookup table containing the 900,000 integers of 7 digits.

The only other suggestion is to migrate to a SQL database that supports CHECK constraints.

Open-source databases that support CHECK constraints include:

• PostgreSQL
• SQLite
• Firebird
• Apache Derby
• HyperSQL

Every commercial database also supports CHECK constraints.

Basically, MySQL is the only SQL database on the market that doesn't support CHECK constraints!