Changing primitive fields and constraints Modify with MARIA DB

Question

The commands Modify and change column are used to change both the primitive fields and the constraints and field name.

alter table pessoa modify column profissao varchar (30) not null;
alter table pessoa change column prof profissao varchar (30) ;

However, when I use:

 alter table pessoa modify column profissao varchar (25) not null default '';

alter table pessoa Modify column professional varchar (25) not null default '' Error Code: 1265. Data truncated for column 'professional' at Row 1 0,083 sec

Answer 1

Your command:

alter table pessoa modify column profissao varchar (25) not null default '';

The problem is trying to apply the restriction not null in a table that already has in this field a value null.

Before performing your structure change command, change the field values profissao in order to satisfy the restriction to be implanted.

update pessoa set 
  profissao = ''
where 
  profissao is null;  

Now that the data already satisfies the restriction, you proceed with changing the structure:
alter table pessoa modify column profissao varchar (25) not null default '';

About the error Error Code: 1265 mentioned:

alter table pessoa Modify column professional varchar (25) not null default 'Error Code: 1265. Data truncated for column 'professional' at Row 1 0,083 sec

The error message displayed does not help to find the real problem, as it suggests that the problem would be the field with 25 characters not support the existing values, that is, there would be some text with more than 25 characters.
It is worth noting that if this field in question currently has 50 characters, for example, and a text containing 35 characters, when trying to decrease the field size to 25 characters, then the error would actually occur 1265. Data truncated indicating that there would be loss of information, since it would be trying to decrease the field size to a number of characters less than some value present in that field.