mysql field value should be greater than zero

user_6931803 0
Viewed 2723

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;
mysql sql
3 Answers

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.

user_55294450

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.

user_11972390

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

user_101387340
Related