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
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.