mysql - MariaDB 创建表 SQL 错误 : 1170

Question

我知道这个问题之前已经被问过很多次，但是我真的很难将这些答案应用到这个问题上。

我正在使用 MySQL Workbench 将数据库从 Postgres 迁移到 MariaDB。

这是我遇到问题的表格

CREATE TABLE IF NOT EXISTS `postgres`.`oldimage` (
  `oi_name` LONGTEXT NOT NULL,
  `oi_archive_name` LONGTEXT NOT NULL,
  `oi_size` INT NOT NULL,
  `oi_width` INT NOT NULL,
  `oi_height` INT NOT NULL,
  `oi_bits` SMALLINT NULL,
  `oi_description` LONGTEXT NULL,
  `oi_user` INT NULL,
  `oi_user_text` LONGTEXT NOT NULL,
  `oi_timestamp` DATETIME NULL,
  `oi_metadata` LONGBLOB NOT NULL,
  `oi_media_type` LONGTEXT NULL,
  `oi_major_mime` LONGTEXT NULL,
  `oi_minor_mime` LONGTEXT NULL,
  `oi_deleted` SMALLINT NOT NULL DEFAULT 0,
  `oi_sha1` LONGTEXT NOT NULL DEFAULT '',
  INDEX `oi_name_archive_name` (`oi_name`(255) ASC, `oi_archive_name`(255) ASC),
  INDEX `oi_name_timestamp` (`oi_name`(255) ASC, `oi_timestamp` ASC),
  INDEX `oi_sha1` (`oi_sha1`(255) ASC),
  CONSTRAINT `oldimage_oi_name_fkey_cascaded`
    FOREIGN KEY (`oi_name`)
    REFERENCES `postgres`.`image` (`img_name`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `oldimage_oi_user_fkey`
    FOREIGN KEY (`oi_user`)
    REFERENCES `postgres`.`mwuser` (`user_id`)
    ON DELETE SET NULL
    ON UPDATE NO ACTION)  

我得到的错误是

BLOB/TEXT column 'oi_name' used in key specification without a key length
SQL Error: 1170  

我试过将 oi_name 设为 PRIMARY KEY，也试过将其更改为 VARCHAR(255)，但都没有成功。
非常感谢任何帮助。

Answer 1

您的问题在

...
CONSTRAINT `oldimage_oi_name_fkey_cascaded`
FOREIGN KEY (`oi_name`)
REFERENCES `postgres`.`image` (`img_name`)
...

来自 Create foreign Key

Index prefixes on foreign key columns are not supported. One consequence of this is that BLOB and TEXT columns cannot be included in a foreign key because indexes on those columns must always include a prefix length.