INT column accepting only positive values

Asked

Viewed 928 times

-1

How to set table in Mysql for that particular column INT accepted only positive values?

mysql database typing int

  • Did any of the answers solve your question? Do you think you can accept one of them? Check out the [tour] how to do this, if you haven’t already. You would help the community by identifying what was the best solution for you. You can accept only one of them. But you can vote on any question or answer you find useful on the entire site (when you have enough score).

2 answers

3

The ideal is to do this type of control before the database, send the wrong data to return an error and have to correct is the worst form of exception use I have ever seen. If you know that can only positive validate this before in the application, simplify it and gain performance and robustness. In fact Mysql is a great database for storage, but bad for dealing with this kind of thing, so it is better to make it very simple and treat everything in the application.

If you want you can document with a restriction in the column, but it will not be respected:

CHECK (coluna > 0)

I put in the Github for future reference.

Do not use unsigned, it does not work either, and will not give error, only truncate the negative, which is not what you want.

  • Otherwise I’m wrong Mysql Engines ignore this Constraint check, maybe the AP needs a Trigger for that.

  • 2

    @cat went to see the documentation and you are right. Good I do not like Mysql for database full Feature, think it good as storage mechanism and this it does well, so worth what I have answered, makes in the application that is better. Trigger can even solve, but it’s Gambi, and it’s another reason to do nothing. Then I think about changing the answer to make it clearer. And to make it clear, unsigned also doesn’t work.

  • He even analyzes it, but ignores it. In the case of Trigger is that the AP did not contextualize the situation and also did not specify which language to use, I would leave in the language even as a domain rule, to change would be easier without having to touch the schema of the table :)

  • In the case of an API, when it receives the post method, the information is recorded in a table called "movement", and later Rigger records the new quantity of the product in the stock table. The product newly recorded in the drive table, has already reached quantity 0 in the stock table, so is recorded negative quantities, in that product. That which I do not desire! As you have commented the ideal is to do the treatment of this validation via application. The ideal would be to run a GET method by obtaining the stock value of that product by checking before writing to the drives table?

  • It depends on the application I don’t know, but it can be.

1

all right?

As the friends above mentioned, MYSQL has no way to limit the acceptance of positive numbers only. If you use "unsigned", what it will do is "convert" your negative number to positive, and this may compromise your database.

The best way to do this limitation is through the language you are using to make the connection to the database. What language are you using, maybe I can help you.

Hugs.

  • I’m making an API that simulates the low stock of products. Through the POST method passing the Json with the information, it is recorded in the table of moves, and posteiormente is downloaded in the stock table through a Rigger in the database. What happens is that in the stock table when a product has already reached quantity 0, when receiving the POST method in the table moves Rigger makes again the low, getting negative stock. I am using the Nodejs + Mysql language.

  • gets this piece of code to try to understand and pass the solution.

Browser other questions tagged mysql database typing int

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