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