How to create a field in a table with pre-registered values?

Asked

Viewed 32 times

0

I need to register in a 'PROJECTS' table the following fields : name, start date, end date, value, participants and risk. The risk field has to be whole with the following restrictions : 0 - low, 1-medium, 2-high. how to do?

mysql

1 answer

1

Hello,

mysql only supports "Enum" type fields to be string. So, for the field risco have these values 0,1,2 would need to be string. There is no way to store in the database and be treated in the database in a "simple" way without creating procedures.

You can even work with "low", "medium" and "high" which is even more intuitive in use.

Having this information in code, you make the necessary treatments.

Table example:

CREATE TABLE `default_table` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `nome` varchar(255) DEFAULT NULL,
  `data_inicio` date DEFAULT NULL,
  `data_termino` date DEFAULT NULL,
  `valor` int(11) NOT NULL DEFAULT '0',
  `participantes` int(11) NOT NULL DEFAULT '0',
  `risco` enum('baixo','medio','alto') DEFAULT 'baixo',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  • Yeah. I also thought I’d do it the way you said. The thing is, they put a fair obligation in this field, saying they had to follow this business rule. Do you think it is possible to register the field in the table as int and create a vector of the same type or some function that assigns at least the positions [{ 0:'low', 1:'medio', 2:'high' }]?

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.