MySQL 如何在现有表中添加外键约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16810801/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add a foreign key constraint in existing table
提问by user2302288
I am having 2 tables first is smsusers(id,fname,lname,userid)'id' is primary key,
useridis unique key
我首先有 2 个表是smsusers(id,fname,lname,userid)“id”是主键，
userid是唯一键
address_detail(id,address,type);
Here I want to add useridof smsusersas foreign key to address_detailtable.
在这里，我想将useridofsmsusers作为外键添加到address_detail表中。
for that i added a column to address_detailas user_id
为此，我添加了一列address_detail作为user_id
ALTER TABLE address_detail add user_id INT( 20 ) default NULL;
Now I am adding foreign key constraint to table
现在我正在向表中添加外键约束
ALTER TABLE `address_detail` add CONSTRAINT address_detail_fk FOREIGN KEY (user_id)
REFERENCES smsusers ('userid') ON UPDATE CASCADE ON DELETE CASCADE;
This step is showing following error
此步骤显示以下错误
#1064 - You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server
version for the right syntax to use near ''userid') ON
UPDATE CASCADE ON DELETE CASCADE' at line 1
回答by Rapha?l Althaus
I would remove the 'on userid in the query
我会删除'查询中的用户 ID
ALTER TABLE `address_detail` add CONSTRAINT address_detail_fk FOREIGN KEY (user_id)
REFERENCES smsusers (userid) ON UPDATE CASCADE ON DELETE CASCADE;
回答by RandomSeed
The REFERENCESclause expects a column name between brackets, but you provided a string (which happens to be a column name, but this is irrelevant). Remove the quotes:
该REFERENCES子句需要括号之间的列名，但您提供了一个字符串（恰好是列名，但这无关紧要）。删除引号：
....
REFERENCES smsusers (userid) -- no quotes
....
回答by Sathish D
There should be back-quotes. Try this:
应该有反引号。尝试这个：
ALTER TABLE `address_detail` ADD CONSTRAINT `address_detail_fk`
FOREIGN KEY (`user_id`) REFERENCES smsusers (`userid`)
ON UPDATE CASCADE ON DELETE CASCADE;
