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
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`);
CONSTRAINT <CONSTRAINT-NAME>
was provided, I was able to drop the constraint using the name thus: ALTER <TABLE> DROP FOREIGN KEY <CONSTRAINT-NAME>
.
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;
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;
Success story sharing
fk_name
andfk_column
I have to insert whaat...?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, andfk_column
is the referencing column you want work as foreign key.