0

Ok, I know there are several such questions already asked, but my case is different.

This is a mysql query I am trying to execute.

ALTER TABLE `test`.`that` DROP FOREIGN KEY `FK_that_new`; 
ALTER TABLE `test`.`that` ADD CONSTRAINT `that_new_FK` FOREIGN KEY `that` (`Col45`) REFERENCES `new` (`Column 1`) ON UPDATE RESTRICT ON DELETE RESTRICT;

The query is generated through my code. When I used the same query through My HeidiSQL workbench, the query runs fine, executes and gives me result, but while using it through my code it throws me an exception with this error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ALTER TABLE test.that ADD CONSTRAINT that_new_FK FOREIGN KEY that (`Col4' at line 1

Bottom Note : Language used PHP, db engine used : PDO. I also tried using USE DB command but still negative result.

Drew
  • 24,851
  • 10
  • 43
  • 78
K Cloud
  • 271
  • 1
  • 5
  • 15
  • you mean it is a multi-query ? – Drew Jun 06 '16 at 19:42
  • well yes its a multi query, with semicolons. If you can do the updation of foreign key in a single query please suggest. – K Cloud Jun 06 '16 at 19:48
  • what programming language – Drew Jun 06 '16 at 20:08
  • core php nothing more, its a simple query running on PDO – K Cloud Jun 06 '16 at 20:12
  • These are things you need to specify for people to have a clue. :p – Drew Jun 06 '16 at 20:15
  • Please see http://stackoverflow.com/a/6461110 – Drew Jun 06 '16 at 20:15
  • Is there a reason why you can't just send two seperate queries in your code? (Hint: there is none, and that is exactly what you need to do, standard queries for pdo can just have 1 query in it, and your error message says just that). Can you add the php code that you use to send this? – Solarflare Jun 06 '16 at 20:28
  • @Solarflare, thanks for the getaway solution for yours, but i want it get done in single queryshot. As I quoted this query is generated, the function which generates it is also doing lot many query generation behind and resulting in a single query. I need it to be done in single shot. I hope you understand. – K Cloud Jun 06 '16 at 20:31
  • Are `routine` or `function` allowed? You could have your PHP use a `CALL (routine)`, and have the Database have the loaded queries. – Bonatti Jun 06 '16 at 20:41
  • yes i tried doing the queries one by one seems the problem is in the first line, its not deleting my foreign key – K Cloud Jun 06 '16 at 22:08

0 Answers0