如何在 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?

推荐答案

很抱歉，这是不可能的.

I am sorry to inform that it's not possible.

根据 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屋！