Question 21890 in Mysql Question posted in
The official MySQL documentation can be found here.

"Foreign key constraint is incorrectly formed" Trying to make a old project work – Mysql

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;
Tags:

2

Answers


  1. 0

    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 STATUSG"  | 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

    Login or Signup to reply.
  2. 0

    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`);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search