gpt4 book ai didi

mysql - 无法在 MySQL 中使用 ALTER TABLE 添加外键

转载 作者:行者123 更新时间:2023-11-30 21:28:38 24 4
gpt4 key购买 nike

我想在表 data_access_tokens 中的 USERNAME 列中添加一个外键,并使其引用父表 users电子邮件

我检查过(1) USERNAME(在子表中)和EMAIL(在父表中)都是VARCHAR(128) NOT NULL;(2) 父表中的EMAIL为主键(3) 两个表都使用默认的数据库引擎和代码页。

为什么以下添加外来的SQL命令失败:

mysql> ALTER TABLE `data_access_tokens`
-> ADD FOREIGN KEY (`USERNAME`)
-> REFERENCES `users` (`EMAIL`)
-> ON DELETE CASCADE;
ERROR 1215 (HY000): Cannot add foreign key constraint

这是父表(用户):

mysql> show create table users;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
`EMAIL` varchar(128) NOT NULL,
`NAME` varchar(255) NOT NULL,
`ENABLED` tinyint(1) NOT NULL,
PRIMARY KEY (`EMAIL`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> desc users;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| EMAIL | varchar(128) | NO | PRI | NULL | |
| NAME | varchar(255) | NO | | NULL | |
| ENABLED | tinyint(1) | NO | | NULL | |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

这是子表(data_access_tokens):

mysql> show create table data_access_tokens;
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data_access_tokens | CREATE TABLE `data_access_tokens` (
`TOKEN` varchar(50) NOT NULL,
`USERNAME` varchar(128) NOT NULL,
`EXPIRATION` datetime NOT NULL,
`CREATION` datetime DEFAULT NULL,
PRIMARY KEY (`TOKEN`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> desc data_access_tokens;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| TOKEN | varchar(50) | NO | PRI | NULL | |
| USERNAME | varchar(128) | NO | | NULL | |
| EXPIRATION | datetime | NO | | NULL | |
| CREATION | datetime | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

最佳答案

在这种情况下,问题在于两个表的字符集不同。 data_access_tokens 中的列是 latin1,但它在 users 中引用的列是 utf8

你可以解决这个问题:

ALTER TABLE data_access_tokens CONVERT TO CHARACTER SET utf8;

与许多 ALTER TABLE 语句一样,这将锁定表并重写它。如果表很大,可能需要一些时间。

关于mysql - 无法在 MySQL 中使用 ALTER TABLE 添加外键,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/57482056/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com