MySQL InnoDB foreign key between different databases

I would like to know if it's possible in InnoDB in MySQL to have a table with foreign key that references another table in a different database ?

And if so, how this can be done ?

40535 次浏览

小开

It's possible : Link to do it

Example (Table1 is in database1 and HelloTable is in database2) :

ALTER TABLE Table1
ADD foreign key FK_table1(ColumnNameFromTable1)
REFERENCES db2.HelloTable(ColumnNameFromHelloTable)

小开

最佳答案

I do not see any limitation on https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html.

So just use otherdb.othertable and you will be good.

小开

Below is how to add a foreign key on table t2, reference from table db1.historial(codh):

alter table t2
add foreign key FK_t2(micod2)
references db1.historial(codh)
on delete cascade
on update cascade;

小开

ALTER TABLE `tablename1`
ADD CONSTRAINT `tablename1_student_id_foreign`
FOREIGN KEY (`tablename1`.`id`)
REFERENCES `db2`.`tablename2`(`id`)
ON DELETE CASCADE ON UPDATE CASCADE;

if we have table calling answers in db1 and student in db2 calling ramiyusu_offline

we must type as below

ALTER TABLE `answers`
ADD CONSTRAINT `answers_student_id_foreign`
FOREIGN KEY (`id`)
REFERENCES `ramiyusu_offline`.`student`(`id`)
ON DELETE CASCADE ON UPDATE CASCADE;