Field of type ENUM empty arrow when not a valid value

Question

Test scenario

Data set: 10.4.11-Mariadb

Table: tabela

Campos: id (INT, PK, NOT NULL), ativo (ENUM('S','N'), NOT NULL)

Instruction

UPDATE tabela SET ativo = 'XXX' WHERE id = 1

Return

1 Row(s) affected, 1 Warning(s): 1265 Data truncated for column 'active' at Row 1 Rows Matched: 1 Changed: 1 Warnings: 1 0.015 sec

---

Problem

The database only accepts the value S or N. But if I make one update and set any other, it will be empty.

---

Doubts

• Why does the database treat this way? It is particular to this database?
• It would have as "lock" not to accept value other than S or N, and otherwise refuse rather than treat as it is being done?
• What other options do we have for fields that "have options" like the ENUM?

Answer 1

Why the database treats this way?

Because he decided to be that way. It could have adopted several other actions, but Mysql likes to be permissive, it’s like Javascript and other languages, the preference is to do something than give error, even if you break the face.

I’m not entirely against it, but the right thing would be to make a mistake and not complete the operation. Because if you set up that you want something validated it should happen, and a enum is a way to restrict certain values.

This mechanism is quite criticized, see: What is the advantage of using the ENUM type?.

It’s private from this database?

Hard to say because there are many others. It is easier to say that the SQL standard does not command this behavior, so it was a choice of this SGDB.

It would have to "lock" to not accept different value of S or N, and otherwise refuse rather than treat how it is being done?

Depending on Mysql version (8.0.16) there is support for CHECK that can send to check if it is in agreement. I do not know if Mariadb supports.

It can work that way too:

SET sql_mode = 'STRICT_ALL_TABLES';

I don’t know all the consequences. Documentation if you want to review all options and change only what is most important to you.

You can do what a lot of people do when you use Mysql, make sure you’re correct in the application before you send it to the database, which is even more efficient, and usually gives you the best user experience, so it’s irrelevant to validate in the database.

When you have an application it has never been a good idea to validate in the database other than in things that only he can do correctly. Though some people prefer to do so.

What other options do we have for fields that "have options" like ENUM?

Only manuals, which seems to be the opposite of what you want.