CODEWITHSUNDEEP
javascriptmysqlsql
Architecd
Architecd

Reputation: 63

How to perform this mysql query correctly?

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
});

Upvotes: 0

Views: 49

Answers (1)

Barmar
Barmar

Reputation: 782105

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;
      }
    });
  }
});

Upvotes: 1

Related Questions