"Foreign key constraint is incorrectly formed" Trying to make a old project work-CodePudding

So i'm trying to make a old project work again but when i put back my tables to the new database i'm having this issue

ERROR 1005 (HY000) at line 777: Can't create table ****.#sql-3f2_45 (errno: 150 "Foreign key constraint is incorrectly formed")

There is the table that causing problems:

CREATE TABLE `install__dashboards` (
  `id` int(11) NOT NULL PRIMARY KEY,
  `zone` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `protocol` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `agent` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `object` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `type` set('dashboard','visualization','search') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Contraintes pour la table `install__dashboards`
--
ALTER TABLE `install__dashboards`
  ADD CONSTRAINT `dash_agent` FOREIGN KEY (`agent`) REFERENCES `install__agents` (`name`),
  ADD CONSTRAINT `dash_proto` FOREIGN KEY (`protocol`) REFERENCES `install__ports` (`protocol`),
  ADD CONSTRAINT `dash_zone` FOREIGN KEY (`zone`) REFERENCES `install__zone` (`slug`);

References tables :

CREATE TABLE `install__agents` (
  `id` int(11) NOT NULL,
  `OS` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `zone` varchar(255) NOT NULL,
  `IP` text,
  `isLog` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `install__ports` (
  `id` int(11) NOT NULL,
  `numero` int(11) NOT NULL,
  `protocol` varchar(255) NOT NULL,
  `isUDP` tinyint(1) NOT NULL DEFAULT '0',
  `machine` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `install__zone` (
  `slug` varchar(255) NOT NULL,
  `status` enum('notpresent','installing','installed') NOT NULL,
  `options` text,
  `IPrange` text,
  `IPsystem` text,
  `system` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CodePudding user response：

To be honest, the error message isn't very descriptive :-(

To get more details about the cause of the error, just check InnoDB status:

$ mysql -e"SHOW ENGINE INNODB STATUS\G"  | grep -C3 "FOREIGN KEY ERROR"


SEMAPHORES
----------
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2022-12-06 11:08:12 0x7f43a80ba700 Error in foreign key constraint of table `test`.`install__dashboards`:
Alter  table `test`.`install__dashboards` with foreign key `dash_proto` constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.------------

So the problem/reason is There is no index in the referenced table where the referenced columns appear as the first columns

CodePudding user response：

You need to create indexes on the referencing column:

CREATE INDEX `ix_install__agents_name` ON `install__agents` (`name`);
CREATE INDEX `ix_install__ports_protocol` ON `install__ports` (`protocol`);
CREATE INDEX `ix_install__zone_slug` ON `install__zone` (`slug`);

but your schema does not look right. It's better to have the ID of the entity.

Something like this:

CREATE TABLE `install__agents` (
  `id` int(11) NOT NULL PRIMARY KEY,
  `OS` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `zone` varchar(255) NOT NULL,
  `IP` text,
  `isLog` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `install__ports` (
  `id` int(11) NOT NULL PRIMARY KEY,
  `numero` int(11) NOT NULL,
  `protocol` varchar(255) NOT NULL,
  `isUDP` tinyint(1) NOT NULL DEFAULT '0',
  `machine` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `install__zone` (
  `id` int(11) NOT NULL PRIMARY KEY,
  `slug` varchar(255) NOT NULL,
  `status` enum('notpresent','installing','installed') NOT NULL,
  `options` text,
  `IPrange` text,
  `IPsystem` text,
  `system` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `install__dashboards` (
  `id` int(11) NOT NULL PRIMARY KEY,
  `zone_id` int(11) DEFAULT NULL,
  `protocol_id` int(11) DEFAULT NULL,
  `agent_id` int(11)DEFAULT  NULL,
  `object` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `type` set('dashboard','visualization','search') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `install__dashboards`
  ADD CONSTRAINT `dash_agent` FOREIGN KEY (`agent_id`) REFERENCES `install__agents` (`id`),
  ADD CONSTRAINT `dash_proto` FOREIGN KEY (`protocol_id`) REFERENCES `install__ports` (`id`),
  ADD CONSTRAINT `dash_zone` FOREIGN KEY (`zone_id`) REFERENCES `install__zone` (`id`);