所选列不具有可兼容类型，即使它具有相同类型 [英] Selected columns dont have compatiable type, even it has same type

问题描述

我使用groupName从Table2在Table1（propType）上创建FOREIGN KEY。但我面临下面的错误。
选择的列不具有兼容类型，即使它在mysql中具有相同的类型

表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 ）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; 
  

表2：

 code> 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` （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; 
  

我正在创建FOREIGN KEY如下

  ALTER TABLE`konysyncconsoledb7214dev`.`configuration_master` 
 ADD CONSTRAINT`FK_configGrop` 
 FOREIGN KEY（`propType`）
 REFERENCES`konysyncconsoledb7214dev`.`config_group `（`groupName`）
  

下面的图片是错误msg。如果有任何面对这个问题plz让我知道我失踪了。

解决方案

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

这篇关于所选列不具有可兼容类型，即使它具有相同类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！