I have a massive database, it has more than 2,000 rows, and the second column has an ENUM that allows only 'Option 1' and 'Option 2'. If I enter 200 rows in the table and write the wrong ENUM name as an example insert into tabela values ('blabla', 'Ocao 2') it will return an empty area in the ENUM field and this would lead to a major headache. Is there any way in SQL to prevent this from happening, like a check structure, preventing me from running the code?
Is it possible to create a decision structure in a table in SQL?
4
asked by anonymous 01.11.2014 / 13:41
1 answer
4
You can use trigger that in events UPDATE and INSERT verify whether the data in the ENUM column is valid or not. If not, generate an exception to be handled in the application.
Another option is to create another table with the possible values of ENUM, and then refer it to the first one through a foreign key, ensuring through integrity constraints that an unsupported value is inserted.
I think it's possible to do this using DOMAIN , but I do not know if MySQL supports this, or how extensible this support is.
01.11.2014 / 13:46