如何在MariaDB或MySQL中创建文本类型的外键? [英] How can I create a foreign keys of text type in MariaDB or MySQL?

问题描述

我有两个表:

CREATE TABLE first_table(
my_id TEXT(6) NOT NULL,
content VARCHAR(30) NOT NULL,
PRIMARY KEY(my_id(6))
) Engine=InnoDB charset utf8mb4 collate utf8mb4_general_ci;

CREATE TABLE second_table(
another_id TEXT(6) NOT NULL,
my_id TEXT(6) NOT NULL,
another_content VARCHAR(30) NOT NULL,
PRIMARY KEY(another_id(6))
) Engine=InnoDB charset utf8mb4 collate utf8mb4_general_ci;

但是在第二个表中，我无法创建引用第一个表的外键，首先，我尝试了此操作:

But in the second table I can't create a foreign key that references the first table, first I've tried this:

ALTER TABLE second_table
ADD FOREIGN KEY (my_id)
REFERENCES first_table(my_id)
ON DELETE CASCADE ON UPDATE CASCADE;

并收到此错误:

ERROR 1170 (42000): BLOB/TEXT column 'my_id' used in key specification without a key length
MariaDB [base_ventas]>

所以，我试图像这样指定密钥长度:

So, I tried to specify the key length like this:

ALTER TABLE second_table
ADD FOREIGN KEY (my_id(6))
REFERENCES first_table(my_id)
ON DELETE CASCADE ON UPDATE CASCADE;

我得到了这个错误:

ERROR 1005 (HY000): Can't create table `base_ventas`.`#sql-1a08_23c` 
(errno: 150 "Foreign key constraint is incorrectly formed")

使用数字ID不会发生这种情况，但是我需要具有字符串类型的ID，可以做到这一点，或者我缺少什么?

This doesn't happen with numeric ids but I need to have string type ids, it can be done, or I am missing something?

推荐答案

很抱歉通知您不可能.

根据 MySql文档:

不支持外键列上的索引前缀.一其结果是不能包含BLOB和TEXT列外键中的索引，因为这些列上的索引必须始终包含前缀长度.

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.

和 MariaDB文档:

子表中的列必须是索引或最左边的部分索引.不支持索引前缀(因此，TEXT和BLOB列不能用作外键)

The columns in the child table must be an index, or the leftmost part of an index. Index prefixes are not supported (thus, TEXT and BLOB columns cannot be used as foreign keys)

如果您确实需要字母数字键，请考虑改用char或varchar之类的东西.最简单，最通用，最常见的方法是使用数字键，例如INT.

If you really need alphanumeric keys, consider using something like char or varchar instead. The easiest, most generic, and most common approach is to use numeric keys, such as INT.

这篇关于如何在MariaDB或MySQL中创建文本类型的外键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！