mysql field value should be greater than zero

Question

following is my table structure, i want to validate price_list_rate field, value should be greater than zero, i tried unsigned

Version information: 4.5.4.1deb2ubuntu2.1

CREATE TABLE `price_list` (
`price_list_id` int(11) NOT NULL,
`price_list_city_id` int(11) NOT NULL,
`price_list_process_id` int(11) NOT NULL,
`price_list_product_id` int(11) NOT NULL,
`price_list_rate` float UNSIGNED NOT NULL,
`price_list_vendor_rate` int(11) NOT NULL,
`created_by` int(10) UNSIGNED NOT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_by` int(10) UNSIGNED NOT NULL,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

It might also be worth pointing out that for a price column, float is not a suitable data type. Decimal would be better. — Martin, Mar 04 '20 at 11:02
Which MySQL version? https://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working — jarlh, Mar 04 '20 at 11:03

Yogesh Sharma · Answer 1 · 2020-03-04T11:15:17.513

5

You need check constraint :

 ALTER TABLE `price_list`
    ADD CONSTRAINT chk_price CHECK (`price_list_rate` > 0);

Note : CHECK constraint is available starts from MySQL 8.0.16 for older version you need trigger that prevents 0 price being inserting.

edited Mar 04 '20 at 11:15

answered Mar 04 '20 at 11:00

Yogesh Sharma

49,870
5
26
52

the table is already created, how to apply query to add ```CHECK``` – Mohit Rathod Mar 04 '20 at 11:02
@MacRathod `ALTER TABLE CHANGE COLUMN`. – Akina Mar 04 '20 at 11:02
@MacRathod [read this](https://stackoverflow.com/a/1356875/3536236) – Martin Mar 04 '20 at 11:03
If MySQL 8.0.16, or later. – jarlh Mar 04 '20 at 11:04
This solution adds table constraint, not field constraint. Although it solves the problem too. – Akina Mar 04 '20 at 11:06
i don't know, how to put trigger for that, can you plz help me?? – Mohit Rathod Mar 04 '20 at 11:16
@MacRathod. . . Trigger would be alternative. – Yogesh Sharma Mar 04 '20 at 11:19
@Akina it's older version, i've just mentioned in question after your suggestion – Mohit Rathod Mar 04 '20 at 11:23

score 0 · Answer 2 · answered Mar 04 '20 at 11:20

MySQL 8.0.16 implemented the SQL CHECK constraint. Using this you can solve this problem

ALTER TABLE `price_list`
ADD CONSTRAINT min_price CHECK (`price_list_rate` > 0);

For earlier version you can use a CHECK constraint using a view WITH CHECK OPTION or a trigger.

Akina · Answer 3 · 2020-03-04T11:39:47.763

0

If your version does not support CHECK constraints then use

CREATE TRIGGER tr_bi_check_price_list_rate 
BEFORE INSERT
ON price_list
FOR EACH ROW
BEGIN
IF NEW.price_list_rate <= 0 THEN
    SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Error: `price_list`.`price_list_rate` must be positive.';
END IF;
END

and the same trigger for BEFORE UPDATE event.

fiddle

edited Mar 04 '20 at 11:39

answered Mar 04 '20 at 11:31

Akina

39,301
5
14
25

mysql field value should be greater than zero

3 Answers3