Foreign Key in another database?

Question

Foreign Key in another database?

Navigation

#1 by (2 votes)
#2 by (0 votes)
#3 by (0 votes)

1

Doubt in MySQL ...

What are the possible problems that can occur when doing a foreign key in different databases?

Considering this type of foreign key and a View that looks for information from another database, which one is most useful?

mysql mysql-workbench

asked by anonymous 31.07.2014 / 19:26

3 answers

Convert Windows application to Windows Phone - C # (HttpWebResponse and HttpWebRequest) How to run all files inside a folder

score 2 · Answer 1

On the first question, foreign key in another database, what is allowed is to make an attribute with the same name and use the same index value in the other database, but mysql does not allow creating relationships between different databases, so do not ensures integrity between tables from different databases 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 the where condition).

About the view is used for queries where you want to "limit" the attributes, hiding some information that you may not want to be viewed by a certain user, to gain performance when searching for a lot of information / complexity, "stored procedure" is used.

score 0 · Answer 2

On your first question, you can not use any foreign key on two different bases, no MySQL engine (database I know, myISAM and etc ... I can not tell you if another database based sql allows) make such a relationship even if they are of the same language and engine (MySQL using myISAM for example), nor is it oriented to do this either, in case you need something to be consulted externally is guided the use of webservices and if any data / table needs to query in some different database is because some error in the modeling of the same was committed, as this hurts good practices of data modeling (to find out more look for the database NF's in google).

On the second question the stored procedure is a hand in the wheel for if you have a query for example with many joins, table relationships and derivatives, it streamlines the search process and allows you to avoid much rewrite of code and depending on which language you are using for back end development along with your server side you can use memcache which helps further.

About views, you just create constraints for your database to display the data that will be presented to it (that user defines it in the connection string to the database).

I hope I have helped you and given a partially satisfactory answer.

score 0 · Answer 3

I do not know if this is a good practice in database administration, but I already did it in MySQL and it works (at least using innodb).

Imagine the following senario:

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 relinking is actually pointing to the (Parent) table with a SHOW CREATE TABLE :

show create table banco2.tabelaY;