If there are lots of fields which would join across databases, then I would say that it is probably a good idea. What you should look at is how much the two databases interact. I suppose there could be reasons to do so, but I am sure you get my point of logically separating data stores where appropriate. For example, I wouldn't necessarily combine place an orders system database and the employee management database together. Logically when groups tables don't fit together, I normally separate them into their own databases. That being said, you shouldn't keep everything in a single database. You have all the benefits of storing data like it is in multiple databases while being able to use the functions of a single database, like foreign keys. With 2k05/2k08 it's definitely better to partition a single database. You can't use conventional FOREIGN KEY syntax to declare such constraints across MySQL instances. Note that you'd have to keep information about the referential integrity constraints in your MySQL Proxy plugin (or write a custom config file for your plugin that records the relationships). If the constraint is intended to support cascading behavior, do that instead of failing.
ADD FOREIGN KEY MYSQL UPDATE
Fail UPDATE if any dependent rows found in child table. If the primary key value is changing as part of the update, query for dependent rows found in child table. Fail DELETE if any dependent rows found in child table. Fail INSERT/ UPDATE if no match found in parent table. Intercept INSERT and UPDATE against child table. You could write a plugin for MySQL Proxy, that manages referential integrity between the parent and child tables on different servers:
I can't think of any way to do this with standard MySQL.
0 kommentar(er)
