Field allowing only an exact amount of data

Question

How to put a field in mysql that only allows 4 characters. No more and no less?

I’m doing like this:

alter table cartoes add os_4_ultimos_digitos int (4); <

But it takes a Constraint and I can’t do it.

Answer 1

To fill an INT value with zeros on the left:

ALTER
   TABLE cartoes
CHANGE
   COLUMN `os_4_ultimos_digitos`
   `os_4_ultimos_digitos` INT(4) ZEROFILL

To add a column already with this feature:

ALTER
   TABLE cartoes
ADD
   os_4_ultimos_digitos INT(4) ZEROFILL;

Alternative with TRIGGER

If you prefer, you can use a VARCHAR field, and force DB not to accept anything other than 4 characters when inserting with a Trigger:

DELIMITER //
CREATE TRIGGER trg_len_4_insert BEFORE INSERT ON cartao
FOR EACH ROW
BEGIN
    DECLARE msg VARCHAR(255);
    IF LENGTH( new.os_4_ultimos_digitos ) != 4 THEN
        SET msg = concat( 'Erro: tamanho errado do campo: ', CAST(new.os_4_ultimos_digitos AS CHAR) );
        SIGNAL SQLSTATE '45000' SET message_text = msg;
    END IF;
END
DELIMITER ;

See this code working here: SQL Fiddle.

Create one for BEFORE UPDATE as well, if applicable

Notes:

• This syntax is valid for Mysql 5.5 or higher
• More options in this Soen post.

Recommending

I posted the above alternatives to answer the question with the Mysql tag, but if you are using some other language for the application, the most appropriate place to treat this is in the application, and not in the structure of DB.

You can keep the INT as is, and handle the data at the time the user fills the data, and while recovering the same from DB, do the padding adequately (which, by the way, is the simplest alternative).

Answer 2

If the field is new:

ALTER TABLE cartoes 
ADD COLUMN os_4_ultimos_digitos CHAR(4);

existing field:

ALTER TABLE cartoes
CHANGE COLUMN os_4_ultimos_digitos CHAR(4);

tries to reduce the name of this field, it is strange