2

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.
ADyson
  • 57,178
  • 14
  • 51
  • 63
Tesfaye
  • 21
  • 1
  • 2
    Float is an approximate value datatype and not suitable for prices , use decimal instead. – P.Salmon Apr 26 '23 at 08:24
  • What have you tried? Have you read [CHECK Constraints](https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html)? Your repeating columns are a clear indication of the need to normalize. – user1191247 Apr 26 '23 at 08: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 Apr 26 '23 at 08: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 Apr 26 '23 at 08: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 Apr 26 '23 at 08:42
  • BTW If you succeed then you can forget the concept of loss leader.. – P.Salmon Apr 26 '23 at 08:42
  • Please run `SELECT VERSION();` and tell us what it says. – Bill Karwin Apr 26 '23 at 15:58

1 Answers1

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
);
Zufar Sunagatov
  • 668
  • 1
  • 6
  • 16