Foreign Key in another database?

Question

Doubt in Mysql...

What are the possible problems that may arise when making a foreign key in different databases?

Taking into consideration this type of foreign key and a View that fetches information from another database, which is more useful?

Answer 1

about the first question, foreign key in another database, what is allowed is to make an attribute with the same name and use the value of equal Indice in the other database, but mysql does not allow creating relationship between different Databases, then does not guarantee integrity between tables of different Database ok.

On the second question, when using primary/foreign keys or indexed attribute will gain time in searching for information when using one of them in the filter ( in Where condition ).

About the view is used for queries where you want to "limit" the attributes, hiding some information that maybe you do not want to be viewed by a certain user, to gain in performance when doing a search for a lot of information/complexity, it is used "stored Procedure".

Answer 2

On your first question, it is not possible to use any foreign key on two different bases, no Mysql engine (database I know, myISAM and etc... I can’t tell you if another database based sql allows) allows you to make such a relationship even if they are of the same language and engine (Mysql using myISAM for example), and nor is it directed to do that either, if you need something to be consulted externally is oriented the use of webservices and if any data / table needs query in some different database is because some error in the modeling of the same has been made, because it hurts the good practices of data modeling (to learn more search the database NF’s on google).

About the second question a stored Procedure is a hand in the wheel for if you have a query for example with many joins, relationships of tables and derivatives, it streamlines the search process and allows you to avoid too much code rewriting and depending on which language you are using for back end development along with your server side you can use the memcache that helps even more.

About the views, it’s just creating constraints for your database to view the data that will be presented to it (that user that defines in the connection string with the database).

I hope I’ve helped you and given a partially satisfactory answer.

Answer 3

I don’t know if this is good practice in database administration, but I’ve done it in MySQL and works (at least using innodb).

Imagine the following senary:

banco1
 - tabelaX (codigo_exemplo_pk)

banco2
 - tabelaY (codigo_exemplo_fk)

The relationship would look like this:

ALTER TABLE banco2.tabelaY ADD FOREIGN KEY (codigo_exemplo_fk) REFERENCES banco1.tabelaX(codigo_exemplo_pk_pk) ON DELETE NO ACTION ON UPDATE CASCADE;

You can check if the relacoinamento is really pointing to the table (Father) with a SHOW CREATE TABLE:

show create table banco2.tabelaY;