MySQL – Cross-Database Foreign Keys vs. Database Design Best Practices

data integritydatabase-designforeign keyMySQL

MySQL allows to create cross-database foreign keys – but is it a good method of ensuring data integrity? Is it possible to give a general answer or is it project-dependent? What are the advantages and what threat does it pose?

Let's say there are two databases, A and B, that in general store different information for different purposes and with different means of uploading data, however some part of the data may appear in both databases. Now let's assume that one wants to make sure that everything out of this small intersection that ends up in B has been previously uploaded to A. Is a foreign key a good idea in this case?

Best Answer

MySQL allows to create cross-database foreign keys - but is it a good method of ensuring data integrity? Is it possible to give a general answer or is it project-dependent? What are the advantages and what threat does it pose?

MySQL doesn't really have a concept of "database". This is because MySQL "evolved" confusing "database" for "schema" and never really fixed it. If you're talking to a real DBA, this is not a cross-database foreign key. So for MySQL, this is perfectly fine and acceptable.

Even in version 8.0 of MySQL,

CREATE SCHEMA is a synonym for CREATE DATABASE.

In PostgreSQL, for instance, we have "schema" and "databases". And cross-schema foreign keys are perfectly fine, but you can't do cross-database fkeys.

psql (9.6.3, server 9.5.6)
You are now connected to database "foobar" as user "ecarroll".
foobar=# CREATE TABLE foo ( id int primary key );
CREATE TABLE
foobar=# INSERT INTO foo SELECT * FROM generate_series(1,10);
INSERT 0 10
foobar=# \connect test;
psql (9.6.3, server 9.5.6)
You are now connected to database "test" as user "ecarroll".
test=# CREATE TABLE foo ( id int references foobar.foo.id, name text );
ERROR:  cross-database references are not implemented: "foobar.foo.id"

That's what a cross-db fkey would look like. Notice it would require explicit schema specification and I'm not aware of any database that implements schema's and databases that permits it.

The downside of the MySQL confusing databases and foreign keys is pretty clear. If you have full access to a single specific database, you do not have the ability to organize your own content. Whereas in PostgreSQL, if you full access to a single database you can create any amount of arbitrary schemas. You don't need SUPER USER nor the ability to CREATE DATABASE

Related Question