Field that only allows an exact amount of data

Navigation
3

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

I'm doing this:

  

alter table cards add os_4_ultimos_digitos int (4);

But a constraint is required and I can not do it.

    
asked by anonymous 19.11.2015 / 13:51

2 answers

3

To fill an INT value with leading zeros: 

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 the DB to accept nothing 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 too, if applicable

Notes:


Recommendation

I put 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 handle this is in the application, not in the DB structure.

You can keep INT as it is, and handle the data at the moment the user populate the data, and when retrieving them from the DB, do the padding properly (which, incidentally, is the simpler alternative).

    
28.11.2015 / 18:28
1

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);

try to reduce the name of this field, it's strange

    
19.11.2015 / 22:01
Doubt in the image gallery Remove a part of the text inside a txt file