Can MySQL InnoDB Foreign Keys Span Multiple Databases?

MySQL InnoDB Foreign Key Referencing Across Databases

In the realm of database management, InnoDB is a widely adopted storage engine for MySQL. It provides robust features like foreign keys to enforce referential integrity. This raises the question: can InnoDB foreign keys span multiple databases?

The answer is a resounding yes! MySQL allows foreign key relationships to reference tables residing in different databases. This capability expands the scope of data integrity constraints, enabling you to establish meaningful relationships across logical database boundaries.

To establish a foreign key relationship across databases, simply use the following syntax:

ALTER TABLE table_name
ADD FOREIGN KEY (column_name)
REFERENCES otherdb.othertable(column_name);

For instance, let's assume we have a table named orders in the sales database and a table named customers in the customer_info database. To create a foreign key relationship from orders.customer_id to customers.id, you would run the following command:

ALTER TABLE orders
ADD FOREIGN KEY (customer_id)
REFERENCES customer_info.customers(id);

This foreign key constraint will ensure that every customer ID in the orders table has a corresponding entry in the customer_info.customers table. This cross-database referential integrity helps maintain data consistency and prevent orphaned rows in either database.

The above is the detailed content of Can MySQL InnoDB Foreign Keys Span Multiple Databases?. For more information, please follow other related articles on the PHP Chinese website!