CODEWITHSUNDEEP
mysqlforeign-keys
slipperyiron
slipperyiron

Reputation: 73

mySQL How to alter column to have foreign key?

I want to alter a column in one of my tables to that it references another column in another table. I've tried to introduce a foreign key the following ways:

ALTER TABLE `table` ALTER COLUMN `id_l` int NOT NULL, foreign key (`id_l`) references table_b(`id_l`);

ALTER TABLE `table` CHANGE `id_l` `id_l` int NOT NULL, foreign key (`id_l`) references table_b(`id_l`);

I am thrown a syntax error.

A work around is to delete the table and create a new one with the desired reference (this works) but I want to change this table as it is, not to transfer the data. How do I do this?

MySQL Server 5.6.17

Upvotes: 6

Views: 21467

Answers (3)

arman amirkamali
arman amirkamali

Reputation: 111

I had problem with other answers. and after some research I find this answer

ALTER TABLE `TABLE_NAME`
ADD COLUMN `COLUMN_NAME` BIGINT(20) UNSIGNED NULL DEFAULT NULL AFTER 
`AFTER_COLUMN_NAME`, 
ADD FOREIGN KEY `FOREIGN_RELATION_NAME`(`COLUMN_NAME`) REFERENCES 
`FOREIGN_TABLE`(`FOREIGN_COLUMN`)  ON UPDATE SET NULL ON DELETE SET NULL

Upvotes: 2

med benzekri
med benzekri

Reputation: 603

if you already have a foreign key and you want to change it you can do :

ALTER TABLE your_table DROP CONSTRAINT your_constraint;

then:

ALTER TABLE your_table ADD FOREIGN KEY (your_column) REFERENCES other_table(other_column);

if you don't know your constraint name you can do SHOW CREATE TABLE mytable;

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

Just use ALTER TABLE along with ADD CONSTRAINT:

ALTER TABLE `table` ADD CONSTRAINT fk_l_id FOREIGN KEY (id_l) REFERENCES table_b(id_l);

Upvotes: 9

Related Questions