Dropping a table error

[lurtnowski]

I ran into this error

when I tried to run

...
DROP TABLE IF EXISTS mcp_manufactures;
DROP TABLE IF EXISTS mcp_models;
DROP TABLE IF EXISTS mcp_names;
DROP TABLE IF EXISTS mcp_types;
DROP TABLE IF EXISTS mcps;
...

But when I look at all the mcp tables

create table mcps (
   mcp_id SMALLINT NOT NULL AUTO_INCREMENT,
   name VARCHAR(50) NOT NULL,
   type VARCHAR(25),
   manufacturer VARCHAR(50),
   model VARCHAR(25),
   capacity VARCHAR(25),
   frequency INT,
   voltage INT,
   notes TEXT NULL,
   created_by VARCHAR(50) DEFAULT 'lurtnowski@industechnology.com',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50) NULL,
   updated_date TIMESTAMP NULL,
   enabled BOOLEAN DEFAULT 1,
   PRIMARY KEY ( mcp_id )
);

create table mcp_names (
   mcp_name_id SMALLINT NOT NULL AUTO_INCREMENT,
   mcp_id SMALLINT,
   name VARCHAR(25),
   FOREIGN KEY ( mcp_id ) REFERENCES mcps ( mcp_id ),
   PRIMARY KEY ( mcp_name_id )
);
create table mcp_types (
   mcp_type_id SMALLINT NOT NULL AUTO_INCREMENT,
   mcp_id SMALLINT,
   name VARCHAR(25),
   FOREIGN KEY ( mcp_id ) REFERENCES mcps ( mcp_id ),
   PRIMARY KEY ( mcp_type_id )
);
create table mcp_manufacturers (
   mcp_manufacturer_id SMALLINT NOT NULL AUTO_INCREMENT,
   mcp_id SMALLINT,
   name VARCHAR(25),
   FOREIGN KEY ( mcp_id ) REFERENCES mcps ( mcp_id ),
   PRIMARY KEY ( mcp_manufacturer_id )
);
create table mcp_models (
   mcp_model_id SMALLINT NOT NULL AUTO_INCREMENT,
   mcp_id SMALLINT,
   name VARCHAR(25),
   FOREIGN KEY ( mcp_id ) REFERENCES mcps ( mcp_id ),
   PRIMARY KEY ( mcp_model_id )
);

I’m confused cause aren’t I supposed to drop tables with FK first, then the table with no FK.
So why the error?

[Andres_Vaquero]

@lurtnowski

I’m confused cause aren’t I supposed to drop tables with FK first, then the table with no FK.
So why the error?

That’s what I thought too… maybe there is some other table with the constraint that you aren’t aware of? May be possible to query for all tables that have that constraint? I’m not an SQL expert so cannot offer much more help but picked this up from stack overflow:

USE information_schema;
SELECT *
FROM
  KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = 'X'
  AND REFERENCED_COLUMN_NAME = 'X_id';

How to find all tables that have foreign keys that reference particular table.column and have values for those foreign keys?

[benanamen]

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS mcp_manufactures;
DROP TABLE IF EXISTS mcp_models;
DROP TABLE IF EXISTS mcp_names;
DROP TABLE IF EXISTS mcp_types;
DROP TABLE IF EXISTS mcps;
SET FOREIGN_KEY_CHECKS = 1;

[fullstackdev]

@lurtnowski

DROP TABLE IF EXISTS mcp_manufactures;

It’s a typo.

In your DROP statement, you have written mcp_manufactures which is missing the last r. It should be mcp_manufacturers. Change that first command to:

DROP TABLE IF EXISTS mcp_manufacturers;

If you check, you’ll see that two tables are still left in your database:

mysql> show tables;
+---------------------+
| Tables_in_sitepoint |
+---------------------+
| mcp_manufacturers   |
| mcps                |
+---------------------+
2 rows in set (0.00 sec)

To figure this out, I added my own test database in MySQL, then used your CREATE and DROP scripts. I got the exact same error as you. Then when I checked using SHOW TABLES, I noticed the typo