Can MySQL InnoDB Foreign Keys Span Across Databases?

MySQL InnoDB Foreign Keys Across Databases

In MySQL InnoDB, you can create foreign keys between tables in different databases. This allows you to enforce referential integrity even when the tables reside in separate databases. To achieve this:

CREATE TABLE example_table (
  id INT PRIMARY KEY,
  other_id INT,
  FOREIGN KEY (other_id) REFERENCES otherdb.othertable(id)
);

In the example above, the example_table in your current database has a foreign key that references the other_id column in the othertable in the otherdb database. The otherdb.othertable must exist and its id column must be a primary key or unique index.

This allows you to maintain referential integrity even if you move the tables to different databases. The MySQL documentation does not specify any limitations on foreign key references across databases. Therefore, you can confidently use the otherdb.othertable syntax to establish cross-database foreign key relationships.

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