当我尝试在表格中添加Check Constraint时没有任何变化

Question

I am trying to add check constraint in my table that prevents adding more data into a table if the sum of rows shop_id is greater than 3. I have written the following code and its just not working. Kindly check this and guide me.

ALTER TABLE kinect_temp_data
ADD CONSTRAINT my_const CHECK (sum(distinct(shop_id))<3)

The above query runs successful,but it does not create any effect and i can still able to add more rows, and when i query this, it display that no check constraint was added.

SHOW CREATE TABLE kinect_temp_data

Output

CREATE TABLE `kinect_temp_data` (
 `cart_number` int(11) NOT NULL AUTO_INCREMENT,
 `product_id` varchar(50) NOT NULL,
 `shop_id` varchar(50) NOT NULL,
 `product_name` varchar(50) NOT NULL,
 `item_number` varchar(50) NOT NULL,
 `image1_path` varchar(50) NOT NULL,
 `image2_path` varchar(50) NOT NULL,
 `image3_path` varchar(50) NOT NULL,
 `price` int(11) NOT NULL,
 PRIMARY KEY (`cart_number`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1

Kindly check this and guide me what i am doing wring here.

Thanks.

Answer 1

MySQL don't support check constraints -- they are ignored.

But you can use BEFORE INSERT and BEFORE UPDATE triggers to realize such functionality.

There is good explanation