如何在单个 MySQL 语句中添加列并使其成为外键？

在 mysql 中，是否可以在同一语句中添加列和外键？添加 fk 的正确语法是什么？

以下是我的 SQL 语句:

ALTER TABLE database.table
ADD COLUMN columnname INT DEFAULT(1),
FOREIGN KEY (fk_name) REFERENCES reftable(refcolumn) ON DELETE CASCADE;

以及相应的错误信息:

您的 SQL 语法有错误; 请检查与您的 MySQL 服务器版本对应的手册，以便在第4行的“ FOREIGN KEY (fk _ name) REFERENCES reftable (refcolumn) ON DELETE CASCADE”附近使用正确的语法

121171 次浏览
小开

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;
小开

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,

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`);
小开

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;
小开

This can be simplified a bit. You just need to add the "ADD" keyword before "FOREIGN KEY". Adding example below.

ALTER TABLE database.table
ADD COLUMN columnname INT DEFAULT(1),
ADD FOREIGN KEY (fk_name) REFERENCES reftable(refcolumn) ON DELETE CASCADE;

提交答案