我有两张桌子:
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;
但在第二个表中,我无法创建引用第一个表的外键,首先我尝试了以下操作:
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]>
因此,我尝试像这样指定密钥长度:
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,这是可以完成的,或者我遗漏了一些东西?