选定的列没有兼容的类型，即使它具有相同的类型

2022-01-20 00:00:00 sql database foreign-keys mysql

我正在使用 groupName 从 Table2 的 Table1(propType) 上创建 FOREIGN KEY.但我面临以下错误.选定的列没有兼容的类型，即使它在 mysql 中具有相同的类型

表 1:

 创建表 `configuration_master` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`insertTimestamp` 日期时间 DEFAULT NULL，`propName` varchar(255) 整理 utf8_unicode_ci 非空，`propValue` varchar(255) 整理 utf8_unicode_ci DEFAULT NULL，`propType` varchar(45) 整理 utf8_unicode_ci DEFAULT NULL，`IsCloudSupport` varchar(45) 整理 utf8_unicode_ci DEFAULT NULL，`DisplayName` varchar(45) 整理 utf8_unicode_ci DEFAULT NULL，`updateTimestamp` 日期时间 DEFAULT NULL,`userId` bigint(20) 默认为空，`SYNCCOL1` varchar(255) 整理 utf8_unicode_ci DEFAULT NULL，`SYNCCOL2` varchar(255) 整理 utf8_unicode_ci DEFAULT NULL，`SYNCCOL3` varchar(255) 整理 utf8_unicode_ci DEFAULT NULL，`SYNCCOL4` varchar(255) 整理 utf8_unicode_ci DEFAULT NULL，`SYNCCOL5` varchar(255) 整理 utf8_unicode_ci DEFAULT NULL，`SYNCCOL6` varchar(255) 整理 utf8_unicode_ci DEFAULT NULL，`SYNCCOL7` varchar(255) 整理 utf8_unicode_ci DEFAULT NULL，`SYNCCOL8` varchar(255) 整理 utf8_unicode_ci DEFAULT NULL，`SYNCCOL9` varchar(255) 整理 utf8_unicode_ci DEFAULT NULL，`SYNCCOL10` varchar(255) 整理 utf8_unicode_ci DEFAULT NULL，`prptyp` int(11) 默认为空，主键(`id`)，唯一键`propName`(`propName`)，KEY `FKB54491EB8E326E43` (`userId`),KEY `proptype_frn_idx` (`propType`),KEY `ASD_idx` (`prptyp`),约束`FKB54491EB8E326E43`外键(`userId`)参考`user_master`(`id`)) 引擎=InnoDB AUTO_INCREMENT=86 默认字符集=utf8 COLLATE=utf8_unicode_ci;

表2:

 创建表 `config_group` (`groupName` varchar(45) 字符集 utf8 非空，`prop1` varchar(200) 整理 utf8_unicode_ci DEFAULT NULL，`prop2` varchar(200) 整理 utf8_unicode_ci DEFAULT NULL，`prop3` varchar(200) 整理 utf8_unicode_ci DEFAULT NULL，`prop4` varchar(200) 整理 utf8_unicode_ci DEFAULT NULL，`ADS` int(11) 默认为空，`config_groupcol` varchar(45) 整理 utf8_unicode_ci DEFAULT NULL，主键(`groupName`)，唯一键`config_groupcol_UNIQUE`(`config_groupcol`)，唯一键 `ADS_UNIQUE` (`ADS`)) 引擎=InnoDB 默认字符集=utf8 排序=utf8_unicode_ci;

我正在创建 FOREIGN KEY 如下

 ALTER TABLE `konysyncconsoledb7214dev`.`configuration_master`添加约束`FK_configGrop`外键(`propType`)参考 `konysyncconsoledb7214dev`.`config_group` (`groupName`)

下图是错误信息.如果有人遇到这个问题，请让我知道我错过了什么.

解决方案

从mysql手册页找到

I am creating FOREIGN KEY on Table1(propType) from Table2 with groupName. But i am facing below error. selected columns dont have compatiable type , even it has same type in mysql

Table 1 :

    CREATE TABLE `configuration_master` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `insertTimestamp` datetime DEFAULT NULL,
      `propName` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `propValue` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `propType` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
      `IsCloudSupport` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
      `DisplayName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
      `updateTimestamp` datetime DEFAULT NULL,
      `userId` bigint(20) DEFAULT NULL,
      `SYNCCOL1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `SYNCCOL2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `SYNCCOL3` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `SYNCCOL4` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `SYNCCOL5` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `SYNCCOL6` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `SYNCCOL7` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `SYNCCOL8` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `SYNCCOL9` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `SYNCCOL10` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `prptyp` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `propName` (`propName`),
      KEY `FKB54491EB8E326E43` (`userId`),
      KEY `proptype_frn_idx` (`propType`),
      KEY `ASD_idx` (`prptyp`),
      CONSTRAINT `FKB54491EB8E326E43` FOREIGN KEY (`userId`) REFERENCES `user_master` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=86 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Table2:

    CREATE TABLE `config_group` (
      `groupName` varchar(45) CHARACTER SET utf8 NOT NULL,
      `prop1` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
      `prop2` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
      `prop3` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
      `prop4` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
      `ADS` int(11) DEFAULT NULL,
      `config_groupcol` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
      PRIMARY KEY (`groupName`),
      UNIQUE KEY `config_groupcol_UNIQUE` (`config_groupcol`),
      UNIQUE KEY `ADS_UNIQUE` (`ADS`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I am creating FOREIGN KEY as below

    ALTER TABLE `konysyncconsoledb7214dev`.`configuration_master` 
    ADD CONSTRAINT `FK_configGrop`
      FOREIGN KEY (`propType`)
      REFERENCES `konysyncconsoledb7214dev`.`config_group` (`groupName`)

Below image is error msg. if any faced this issue plz let me know what i am missing.

解决方案

From the mysql manual page found here

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

Your issue was the collation. I had no problem creating the following whatsoever. Note that I had to ditch your db name from part of it.

Oh, and since you did not provide the third table, I had to remove that FK constraint. But that is not the issue.

Note, nullability was not the issue.

What you decide to do about your collation choices is up to you. But that was it.

CREATE TABLE `configuration_master` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `insertTimestamp` datetime DEFAULT NULL,
  `propName` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `propValue` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `propType` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `IsCloudSupport` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `DisplayName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `updateTimestamp` datetime DEFAULT NULL,
  `userId` bigint(20) DEFAULT NULL,
  `SYNCCOL1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `SYNCCOL2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `SYNCCOL3` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `SYNCCOL4` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `SYNCCOL5` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `SYNCCOL6` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `SYNCCOL7` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `SYNCCOL8` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `SYNCCOL9` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `SYNCCOL10` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `prptyp` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `propName` (`propName`),
  KEY `FKB54491EB8E326E43` (`userId`),
  KEY `proptype_frn_idx` (`propType`),
  KEY `ASD_idx` (`prptyp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



 CREATE TABLE `config_group` (
  `groupName` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `prop1` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `prop2` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `prop3` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `prop4` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ADS` int(11) DEFAULT NULL,
  `config_groupcol` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`groupName`),
  UNIQUE KEY `config_groupcol_UNIQUE` (`config_groupcol`),
  UNIQUE KEY `ADS_UNIQUE` (`ADS`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;   

ALTER TABLE  `configuration_master` 
ADD CONSTRAINT `FK_configGrop`
  FOREIGN KEY (`propType`)
  REFERENCES `config_group` (`groupName`);

More detail of your Original

This addresses the comment from Barmar. Your create table for config_group caused a discrepancy for matching collations for the FK to succeed with the ALTER TABLE . Manual page for SHOW FULL COLUMNS

From the Manual Page here:

If CHARACTER SET X is specified without COLLATE, character set X and its default collation are used.
CREATE TABLE t1
(
    col1 CHAR(10) CHARACTER SET utf8
) CHARACTER SET latin1 COLLATE latin1_bin;
The character set is specified for the column, but the collation is not. The column has character set utf8 and the default collation for utf8, which is utf8_general_ci. To see the default collation for each character set, use the SHOW COLLATION statement.

相关文章