Reputation: 417
I want to refer an exact number of foreign keys of one table to one primary key in another table.as an example if we get the exact number as three.one primary key should connect just three foreign keys in another table. I use USER table to store user details and PRODUCT table to store users' buying products details. if USER table has the primary key as ID and PRODUCT table has foreign key reference to USER table via User_ID column. one user id(ID column in USER table) should limit to an exact number of three records in PRODUCT table.simply, the user can buy at most three product, not beyond that. can I do that in MySQL?
Upvotes: 1
Views: 241
Reputation: 562260
You would have to write a trigger to do this in MySQL. Here's an example:
mysql> DELIMITER ;;
mysql> CREATE TRIGGER limit123 BEFORE INSERT ON Product
FOR EACH ROW BEGIN
IF ((SELECT COUNT(*) FROM Product WHERE user_id = NEW.user_id) > 2)
THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Only three products per user allowed';
END IF;
END;;
mysql> DELIMITER ;
mysql> INSERT INTO Product SET user_id=1;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Product SET user_id=1;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Product SET user_id=1;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Product SET user_id=1;
ERROR 1644 (45000): Only three products per user allowed
Upvotes: 2