How to add a column and make it a foreign key in single MySQL statement?

user_187349 0
Viewed 118131

In mysql, can I add a column and foreign key in the same statement? And what is the proper syntax for adding the fk?

Here is my 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

mysql sql
4 Answers

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;
user_99491900

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;
user_93101310
Related