5

In SQL Server I used the following code to drop a table with a constraint:

IF OBJECT_ID('EMPLOYEES') IS NOT NULL
  BEGIN
         ALTER TABLE EMPLOYEES DROP CONSTRAINT EMP_DEPT_FK
         DROP TABLE EMPLOYEES;
  END

How can I accomplish the same thing in Mysql?

  • Just to show how i managed to do it: i used this command: SET FOREIGN_KEY_CHECKS=0; DROP TABLE IF EXISTS EMPLOYEES; SET FOREIGN_KEY_CHECKS=1; – andrea Feb 12 '17 at 22:35
5

You can use information_schema to check if the table exists but in MySQL there is a simpler method:

DROP TABLE IF EXISTS employees ;
  • The DROP TABLE IF EXISTS syntax is also supported in SQL Server 2016 onwards. – Dan Guzman Feb 12 '17 at 17:06
  • So Simple... i thought i couldn't just do that. What seems strange to me is that mysql doesn't check for constraint before drop the table. In sqlserver i have to delete constraint before dropping a table. Anyway, thank you. – andrea Feb 12 '17 at 17:37
  • 1
    No, it checks and drops the constraints with the table. And if other tables have constraints that reference the one you drop, you'll get an error. – ypercubeᵀᴹ Feb 12 '17 at 17:55
  • Does SQL Server ned to drop the constraints first before dropping a table? I can't test right now but I'd think otherwise. – ypercubeᵀᴹ Feb 12 '17 at 18:15
  • 1
    My necessity was to drop the table to perform a fresh install. Doing so i have the necessity to check before if the table already exists, and if so, drop the table and recreate it. To check if the table already exists i used to do that way in sqlserver. I was looking to find a way to do the same in mysql. – andrea Feb 12 '17 at 22:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.