CODEWITHSUNDEEP
mysqlsqlstored-proceduresphpmyadmin
Wonx2150
Wonx2150

Reputation: 149

Store procedure and foreign key checks

I am trying to create a store procedure to insert data into my joining table which contains two foreign keys (one for members and one for centres)

With straight SQL (via phpmyadmin) i can change values in the table, however with a store procedure I just get error: 1452.

How can I write to the table without causing the error and without disabling the foreign key constraints completely. I understand there's nothing to prevent people from entering the wrong values, the idea is that the user interface will prevent that ultimately. However, if there's a better way within the database I’d love to hear it.

The sql that works is: 

 INSERT INTO `tblmembers_has_tblcentres` (`tblMembers_MemberID`, 
 `tblCentres_CentreID`, `DateMemberJoinedCentre`) VALUES ('92', '2', 
 CURRENT_TIMESTAMP);

And my store procedure looks like this:

CREATE DEFINER=`root`@`localhost` PROCEDURE `Add_Memb_to_Centre`(IN `iMember` 
INT, IN `iCentre` INT)
    SQL SECURITY INVOKER
INSERT INTO `tblmembers_has_tblcentres` (`tblMembers_MemberID`, 
`tblCentres_CentreID`, `DateMemberJoinedCentre`) VALUES ('iMember', 'iCentre', 
CURRENT_TIMESTAMP)

Upvotes: 0

Views: 1100

Answers (1)

Psi
Psi

Reputation: 6793

You are trying to insert the string values "iMember" and "iCentre" rather than their variable contents. Keep using the backticks:

CREATE DEFINER=`root`@`localhost` PROCEDURE `Add_Memb_to_Centre`(IN `iMember` 
INT, IN `iCentre` INT)
    SQL SECURITY INVOKER
INSERT INTO 
    `tblmembers_has_tblcentres` 
    (
       `tblMembers_MemberID`, 
       `tblCentres_CentreID`, 
       `DateMemberJoinedCentre`
    ) 
    VALUES ('iMember', 'iCentre', CURRENT_TIMESTAMP) -- WRONG
    VALUES (`iMember`, `iCentre`, CURRENT_TIMESTAMP) -- CORRECT

Upvotes: 1

Related Questions