77

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 ?

Vikrant Kashyap
  • 5,900
  • 3
  • 29
  • 50
Alaa
  • 4,321
  • 11
  • 48
  • 67

4 Answers4

77

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.

Jairo Lozano
  • 3,035
  • 3
  • 17
  • 25
BarsMonster
  • 6,395
  • 2
  • 33
  • 47
  • 2
    @Code4R7 Make sure the tables use the same engine, as by the [documentation](https://mariadb.com/kb/en/library/foreign-keys/): "_The parent and the child table must use the same storage engine[...]"_ To change table's engine, `ALTER TABLE my_table ENGINE = InnoDB;` Worked for MariaDB 10.1.16. – iloo Dec 20 '17 at 09:20
29

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)
Spilarix
  • 1,388
  • 12
  • 23
  • 13
    +1 Must add that if in addition to the link, had you summarized the contents of the link in the answer here, would be a bit more helpful! – Jagmag Oct 11 '10 at 09:41
  • 3
    -1 A link is not enough, answer should contain more information (explanation or example). – automatix May 19 '14 at 11:38
7

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;
rink.attendant.6
  • 40,889
  • 58
  • 100
  • 149
Salim
  • 371
  • 7
  • 14
0
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;
Hosam Elzagh
  • 1,032
  • 14
  • 25