How to perform this mysql query correctly?

Question

I'm using the npm module 'mysqljs' and trying to perform a query via it.

The purpose of it is to check whether a certain constraint or foreign key exists and to drop it if it does.

Unfortunately I get the following error and can't figure out what's wrong:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ' at line 1

The code looks as followed (shortened version of course):

const query = `
  IF EXISTS (
    SELECT * 
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
    WHERE CONSTRAINT_NAME LIKE "Location_User";
  ) THEN
    ALTER TABLE Location DROP CONSTRAINT Location_User;
  END IF
  `

connection.query(query, (error, results) {
  if (error) throw error;
  return results
});

[This](https://stackoverflow.com/a/5528932/11921403) might be of help — Shahzad, Dec 30 '19 at 15:58
Does this answer your question? [Usage of MySQL's "IF EXISTS"](https://stackoverflow.com/questions/5528854/usage-of-mysqls-if-exists) — popcorn, Dec 30 '19 at 16:02
You need to put this code in a stored procedure, you can't use `IF` statements in ordinary queries. The alternative is performing the two queries separately, with the `if` logic in JavaScript. — Barmar, Dec 30 '19 at 17:28

score 1 · Accepted Answer · answered Dec 30 '19 at 17:34

You can't use IF statements outside of stored procedures.

Do the logic in JavaScript instead.

connection.query(`SELECT COUNT(*) AS count
                  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
                  WHERE CONSTRAINT_NAME LIKE "Location_User";`, (error, results) => {
  if (error) {
    throw error;
  }
  if (results[0].count > 0) {
    connection.query('ALTER TABLE Location DROP CONSTRAINT Location_User;', (error) => {
      if (error) {
        throw error;
      }
    });
  }
});

How to perform this mysql query correctly?

1 Answers1