How to use check constraints in mysql table?

Question

The values of produce_prices must be < wholesaler_prices must be < retailer_prices.

Can I apply check constraints for these to happen?

The table is the following:

CREATE TABLE `crop_products_price` (
  `ID` int(11) NOT NULL,
  `office_type_id` int(11) NOT NULL,
  `established_markets_id` int(11) NOT NULL,
  `weekdays_id` int(11) NOT NULL,
  `crop_products_category_id` int(11) NOT NULL,
  `crop_products_id` int(11) NOT NULL,
  `units_id` int(11) NOT NULL,
  `producer_price1` float NOT NULL,
  `producer_price2` float NOT NULL,
  `producer_price3` float NOT NULL,
  `wholesale_price1` float NOT NULL,
  `wholesale_price2` float NOT NULL,
  `wholesale_price3` float NOT NULL,
  `retail_price1` float NOT NULL,
  `retail_price2` float NOT NULL,
  `retail_price3` float NOT NULL,
  `date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`

The values of producer_price1, producer_price2, producer_price3 must be less than the values of wholesale_price1, wholesale_price2, wholesale_price3
and the values of wholesale_price1, wholesale_price2, wholesale_price3 must be less than the values of retail_price1, retail_price2, retail_price3.

Float is an approximate value datatype and not suitable for prices , use decimal instead. — P.Salmon
– P.Salmon, Commented Apr 26, 2023 at 8:24
What have you tried? Have you read CHECK Constraints? Your repeating columns are a clear indication of the need to normalize. — user1191247
– user1191247, Commented Apr 26, 2023 at 8:25
'Can I apply check constraints for these to happen?' -Yes, if you are on a suitable mysql version . Check constraints support multiple tests using and/or. — P.Salmon
– P.Salmon, Commented Apr 26, 2023 at 8:34
@user1191247: The table does not violate database normalization rules. In fact, this structure guarantees there to be exactly these nine prices per row. This cannot be achieved with child tables as far as I know. — Thorsten Kettner
– Thorsten Kettner, Commented Apr 26, 2023 at 8:38
Apart from that normalization remark I am with user1191247 here. It is not clear, if you even tried to apply a check constraint. It seems straight forward; add a constraint to the table and try to insert a row that violates the check. Have you tried that? If so, what was the result? In other words: What is it exactly that you want to know from us? — Thorsten Kettner
– Thorsten Kettner, Commented Apr 26, 2023 at 8:42

Zufar Sunagatov · Accepted Answer · 2023-04-26 09:08:15Z

0

How to use check constraints in mysql table?

The values of producer_price1, producer_price2, producer_price3 must be less than the values of wholesale_price1, wholesale_price2, wholesale_price3

and the values of wholesale_price1, wholesale_price2, wholesale_price3 must be less than the values of retail_price1, retail_price2, retail_price3.

You can see my example of check_prices constraints on the crop_products_price table:

ALTER TABLE `crop_products_price`
ADD CONSTRAINT `check_prices`
CHECK (
    producer_price1 < wholesale_price1 AND
    wholesale_price1 < retail_price1 AND
    producer_price2 < wholesale_price2 AND
    wholesale_price2 < retail_price2 AND
    producer_price3 < wholesale_price3 AND
    wholesale_price3 < retail_price3
);

answered Apr 26, 2023 at 9:08

Zufar Sunagatov

1,3261 gold badge8 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Tesfaye Over a year ago

I inserted a row that violates the check. However, the solution didn't work, the table accepted the values without error.

Zufar Sunagatov Over a year ago

@Tesfaye let me check, I will fix it

Zufar Sunagatov Over a year ago

@Tesfaye Check if the check constraint is enabled: SELECT CONSTRAINT_NAME, CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, IS_ENABLED FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'crop_products_price' AND CONSTRAINT_NAME = 'check_prices';

Zufar Sunagatov Over a year ago

@Tesfaye constraint is enabled?

Collectives™ on Stack Overflow

How to use check constraints in mysql table?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related