1

Can a foreign key in one MySQL database/schema refer to a table in a different database/schema?

If so, what limitations are there on doing this?

closed as off-topic by Colin 't Hart, Erik Darling, Joe Obbish, Mark Sinkinson, Vérace Dec 21 '17 at 14:24

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Colin 't Hart, Erik Darling, Joe Obbish, Mark Sinkinson, Vérace
If this question can be reworded to fit the rules in the help center, please edit the question.

3

Yes, you just need to specify the schema before the table name. For example, if the referencing table is in schema qa and the referenced table in xx:

ALTER TABLE qa.test_results 
    ADD COLUMN file INTEGER,
    ADD FOREIGN KEY (file) REFERENCES xx.files (file) ;

Then all of your queries will also need to be formatted using schema.table

  • Correct except for the code. The syntax would not add a foreign key. Edited and fixed. – ypercubeᵀᴹ Dec 21 '17 at 15:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.