mariadb “外部索引键条件约束格式不正确”尝试让旧项目运作

apeeds0o  于 2022-12-13  发布在  其他
关注(0)|答案(2)|浏览(98)

因此,我尝试使旧项目再次工作,但当我将表放回新数据库时,我遇到了这个问题
第777行出现错误1005(HY000):无法创建表****#sql-3f2_45(错误号:150“外键约束的格式不正确”)
有一个表导致了问题:
第一个
参考表:

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;
vtwuwzda

vtwuwzda1#

老实说,错误消息不是很有描述性:-(
要获得有关错误原因的更多详细信息,只需检查InnoDB状态:

$ 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.------------

所以问题/原因是There is no index in the referenced table where the referenced columns appear as the first columns

ycl3bljg

ycl3bljg2#

您需要在引用列上创建索引:

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`);

但是您的模式看起来不对,最好使用实体的ID
大概是这样的:

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`);

相关问题