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

Question

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

Answer 1

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;

Answer 2

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

Answer 3

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;

Answer 4

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;