112

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

Lucky
  • 16,787
  • 19
  • 117
  • 151
VinnieP
  • 1,121
  • 2
  • 7
  • 3

4 Answers4

125

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;
Asaph
  • 159,146
  • 25
  • 197
  • 199
  • 7
    And for `fk_name` and `fk_column` I have to insert whaat...? – C4d Aug 13 '15 at 09:39
  • 8
    @C4ud3x `fk_name` will be a name you'd like to give this particular constraint for the purpose of identification (thats optional in query) mysql autogenerates it aswell, and `fk_column` is the referencing column you want work as foreign key. – Rabia Naz khan Sep 11 '15 at 10:36
  • 4
    I would advice everyone to explicitly specify constraint name. Nasty problems can find you if you want to delete that column in the future, and your system runs already in different dbs that have different ideas on how to automatically name the constraints (mariaDB vs mySQL, for example). I wanted to spare this one line of code and did not specified the constraint, like here.Some months after I pay the price the by trying to write a migration that would delete the column in all systems. https://stackoverflow.com/questions/55374835/sql-cannot-drop-foreign-key-due-to-auto-generated-constraint – Alkis Mavridis Apr 11 '19 at 10:06
  • Worked it perfectly. Keep going. – Ashiful Islam Prince Oct 25 '22 at 09:42
54

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`);
Lucky
  • 16,787
  • 19
  • 117
  • 151
  • This should be the selected answer, as this is the script that creates foreign key with the constraint-name *FK_CUSTOMER_TABLE_CUSTOMER_ID*. Only when the `CONSTRAINT ` was provided, I was able to drop the constraint using the name thus: `ALTER DROP FOREIGN KEY `.
     – Subhash Chandran Jul 05 '21 at 18:38
10

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;
Satendra Rawat
  • 1,264
  • 11
  • 23
-3

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;
Rosário Pereira Fernandes
  • 11,015
  • 6
  • 55
  • 79
Aabid Ansar
  • 11
  • 1