如何在单个 MySQL 语句中添加一列并使其成为外键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1545253/
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 column and make it a foreign key in single MySQL statement?
提问by VinnieP
In mysql, can I add a column and foreign key in the same statement? And what is the proper syntax for adding the fk?
在mysql中，我可以在同一个语句中添加列和外键吗？添加 fk 的正确语法是什么？
Here is my SQL:
这是我的 SQL：
ALTER TABLE database.table
ADD COLUMN columnname INT DEFAULT(1),
FOREIGN KEY (fk_name) REFERENCES reftable(refcolumn) ON DELETE CASCADE;
...and the accompanying error message:
...以及随附的错误消息：
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 'FOREIGN KEY (fk_name) REFERENCES reftable(refcolumn) ON DELETE CASCADE' at line 4
您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 4 行的“FOREIGN KEY (fk_name) REFERENCES reftable(refcolumn) ON DELETE CASCADE”附近使用的正确语法
回答by Asaph
Try this:
尝试这个：
ALTER TABLE database.table
ADD COLUMN columnname INT DEFAULT(1),
ADD FOREIGN KEY fk_name(fk_column) REFERENCES reftable(refcolumn) ON DELETE CASCADE;
回答by Lucky
The following query adds a column by alter query and the constraint query makes it a FK in a single mysql query. You can do it like this,
以下查询通过更改查询添加一列，约束查询使其成为单个 mysql 查询中的 FK。你可以这样做，
SYNTAX:
句法：
ALTER TABLE `SCHEMANAME`.`TABLE1`
ADD COLUMN `FK_COLUMN` BIGINT(20) NOT NULL,
ADD CONSTRAINT `FK_TABLE2_COLUMN` FOREIGN KEY (`FK_COLUMN`)
REFERENCES `SCHEMANAME`.`TABLE2`(`PK_COLUMN`);
EXAMPLE:
例子：
ALTER TABLE `USERDB`.`ADDRESS_TABLE`
ADD COLUMN `USER_ID` BIGINT(20) NOT NULL AFTER `PHONE_NUMBER`,
ADD CONSTRAINT `FK_CUSTOMER_TABLE_CUSTOMER_ID` FOREIGN KEY (`USER_ID`)
REFERENCES `USERDB`.`CUSTOMER_TABLE`(`CUSTOMER_ID`);
回答by Satendra Rawat
This can be simplified a bit. You just need to add the "ADD" keyword before "FOREIGN KEY". Adding example below.
这可以简化一点。您只需要在“FOREIGN KEY”之前添加“ADD”关键字。下面添加示例。
ALTER TABLE database.table
ADD COLUMN columnname INT DEFAULT(1),
ADD FOREIGN KEY (fk_name) REFERENCES reftable(refcolumn) ON DELETE CASCADE;
回答by Aabid Ansar
You can use it.
你可以使用它。
ALTER TABLE database.table
ADD COLUMN columnname INT DEFAULT(1);
ALTER TABLE database.table add FOREIGN KEY (fk_name) REFERENCES reftable(refcolumn) ON DELETE CASCADE;
