79

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
  • 6,398
  • 3
  • 32
  • 52
Alaa
  • 4,471
  • 11
  • 50
  • 67

4 Answers4

78

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,883
  • 3
  • 20
  • 29
BarsMonster
  • 6,483
  • 2
  • 34
  • 47
  • 3
    @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
30

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,418
  • 1
  • 13
  • 24
  • 14
    +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
  • 44,500
  • 61
  • 101
  • 156
Salim R
  • 343
  • 7
  • 16
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;
xpredo
  • 1,282
  • 17
  • 27