MySQL InnoDB foreign key between different databases

Question

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 ?

Solution 1

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.

Solution 2

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)

Solution 3

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;

Comments

[Jagmag]

+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!

[automatix]

-1 A link is not enough, answer should contain more information (explanation or example).

[iloo]

@Code4R7 Make sure the tables use the same engine, as by the documentation: "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.