Home > Database > Mysql Tutorial > body text

Can MySQL InnoDB Foreign Keys Span Different Databases?

Linda Hamilton
Release: 2024-11-14 13:45:02
Original
964 people have browsed it

Can MySQL InnoDB Foreign Keys Span Different Databases?

MySQL InnoDB Foreign Key Referencing Another Database

In MySQL InnoDB, it is possible to establish foreign key relationships between tables in different databases. This feature allows you to enforce referential integrity across database boundaries, ensuring data consistency.

Method:

To create a foreign key that references a table in a different database, simply specify the database name followed by the table name in the FOREIGN KEY clause. The syntax is as follows:

ALTER TABLE your_table
ADD FOREIGN KEY (your_column) REFERENCES otherdb.othertable(other_column);
Copy after login

For example, consider two databases: db1 and db2. Suppose we have a table users in db1 and a table orders in db2. We can establish a foreign key in the orders table to reference the users table as follows:

ALTER TABLE orders
ADD FOREIGN KEY (user_id) REFERENCES db1.users(id);
Copy after login

This foreign key will ensure that every user_id in the orders table has a corresponding id in the users table in db1.

Note:

The databases involved in the foreign key relationship must be on the same server. Cross-server foreign keys are not supported in MySQL InnoDB.

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

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template