How do I create Inter-Database Foreign Key Relationships in MySQL InnoDB?

Inter-Database Foreign Key Relationships in MySQL InnoDB

MySQL InnoDB provides the ability to establish foreign key constraints across tables residing in different databases. This functionality allows you to maintain data integrity when referencing data from external источников.

Creating Inter-Database Foreign Keys

To create an inter-database foreign key, simply specify the target table in the other database using its fully qualified name, such as:

ALTER TABLE my_table
ADD FOREIGN KEY (fk_column)
REFERENCES other.other_table(pk_column)

In this example, other is the name of the other database, other_table is the target table, pk_column is the primary key in the target table, and fk_column is the foreign key column in my_table.

Example

Suppose you have two databases, sales and customers, and you want to create an inter-database foreign key from the orders table in the sales database to the customers table in the customers database. You can do this using the following SQL statement:

ALTER TABLE sales.orders
ADD FOREIGN KEY (customer_id)
REFERENCES customers.customers(customer_id)

Now, any record in the orders table that references a non-existent customer in the customers table will be considered invalid and will not be inserted into the database.

Limitations

It's important to note that there are no documented limitations on creating inter-database foreign keys in InnoDB. However, it's recommended to consider the performance implications of such relationships, especially when the databases reside on different servers.

The above is the detailed content of How do I create Inter-Database Foreign Key Relationships in MySQL InnoDB?. For more information, please follow other related articles on the PHP Chinese website!