Dil
Dil

Reputation: 417

how to restrict one table primary key should reference to another table exact number of foreign key

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

Answers (1)

Bill Karwin
Bill Karwin

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

Related Questions