errno: 150 "Foreign key constraint is incorrectly formed") MariaDB

Question

When I run the following script:

 DROP TABLE IF EXISTS `schemaname`.`tablename` ;

 CREATE TABLE IF NOT EXISTS `schemaname`.`tablename` (
   `id` INT(11) NOT NULL AUTO_INCREMENT,
   `ip` VARCHAR(25) NOT NULL,
   `address` TEXT NULL DEFAULT NULL,
 PRIMARY KEY (`id`))
 ENGINE = InnoDB
 DEFAULT CHARACTER SET = latin1;

It returns these error:

Error Code: 1005. Can't create table `schemaname`.`tablename` (errno: 150 "Foreign key constraint is incorrectly formed")

There was a table with the same name but a different structured that I deleted and also I deleted the tables that had foreign key with it.

If I try to create it with another name, then it works. Why can't I create a table with the same name with that one that I deleted?

Answer 1

Find and remove the foreign key relation(s) between those tables you deleted.

It seems that MariaDB didn't do that automatically for you when you deleted the tables.

Find foreign key relations:

SELECT
  CONSTRAINT_NAME, 
  TABLE_NAME, 
  COLUMN_NAME, 
  REFERENCED_TABLE_NAME, 
  REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = '<table>' OR TABLE_NAME = '<table>';

Drop relation:

DROP FOREIGN KEY <constraint_name>;