MySQL 错误 1215 : Cannot add foreign key constraint for geometry datatype POINT

Question

我正在尝试使用列 latlong 创建外键约束，如图所示，但我收到带有给定代码的错误代码 (1215)。如果我更改 latlong 的数据类型对于 int，代码可以正常运行，这让我意识到这个问题的原因是创建外键约束时的 POINT 数据类型。鉴于 POINT 是一个零维几何对象，它不能用默认值实例化，所以我知道这也不是问题。

POINT数据类型一定有一些我不知道的特殊属性，但是网上可用的资源并没有帮助我解决这个问题。

当前代码是:

CREATE TABLE IF NOT EXISTS `notablesurroundingareas` (  
  `nsa_id` int(5) NOT NULL DEFAULT 0,  
  `latlong` POINT NOT NULL,  
  `nsa_location_type` varchar(20) DEFAULT '',  
  `nsa_location_name` varchar(30) DEFAULT '',  
  PRIMARY KEY (`nsa_id`),  
  KEY (`latlong`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `locationinformation` (  
  `latlong` POINT NOT NULL,  
  `li_city` varchar(30) DEFAULT '',  
  `li_zip_code` int(5) DEFAULT 0,  
  `li_area_code` int(3) DEFAULT 0,  
  FOREIGN KEY (`latlong`)   
  REFERENCES notablesurroundingareas(`latlong`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

如上所述，当我更改 POINT 外键 latlong 的数据类型时至int(5) NOT NULL DEFAULT 0代码按预期运行。

Answer 1

有趣:

https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

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.

https://dev.mysql.com/doc/refman/5.6/en/gis-data-formats.html (关于POINT)

Well-Known Binary (WKB) Format The Well-Known Binary (WKB) representation of geometric values is used for exchanging geometry data as binary streams represented by BLOB values

或

Well-Known Text (WKT) Format The Well-Known Text (WKT) representation of geometry values is designed for exchanging geometry data in ASCII form.

看来这就是你不能将其用作 FK 的原因。它们要么表示为 Text，要么表示为 Blob，两者都是不允许的。