Can foreign keys reference tables in another database?
I am trying to maintain one master list of countries and the states within those countries. I'd like to keep this info in a common database, to be used by 2 or three other databases on the same host, using foreign keys to make sure that all the country/state data matches up to the master list. If I can't use foreign keys, should I just run regular updates to sync the data between duplicate tables of countries and states in each database, or is there an better method that I'm not seeing? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can foreign keys reference tables in another database?
sol beach wrote: Why in the world are you trying to keep multiple copies of the data? Why can't you just access the 'master database'? On 12/16/05, Scott Plumlee [EMAIL PROTECTED] wrote: I am trying to maintain one master list of countries and the states within those countries. I'd like to keep this info in a common database, to be used by 2 or three other databases on the same host, using foreign keys to make sure that all the country/state data matches up to the master list. If I can't use foreign keys, should I just run regular updates to sync the data between duplicate tables of countries and states in each database, or is there an better method that I'm not seeing? Sorry, I didn't explain very well. I have a db I'll call Common. In there, I'd like to keep two tables, tblCountry and tblState. Then I've got another database, called Application, with a table called tblAddress. In the Application database, I'd like to reference tblCountry and tblStateProvince. When someone fills out form info that is going to be stored in tblAddress, I want the choices to be pulled from tblCountry and tblStateProvince. When I've done this inside only one database, I've always used foreign keys between the Address.State field and the tblState.State fields. My goal IS to only keep one set of country and state data for multiple databases. But I don't know if I can do that using foreign keys when things are in two different databases - it doesn't appear that I can. So my question is am I better off NOT using foreign keys but doing everything else the same or using foreign keys and having to jerryrig some sort of syncing? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can foreign keys reference tables in another database?
Scott Plumlee wrote: sol beach wrote: Why in the world are you trying to keep multiple copies of the data? Why can't you just access the 'master database'? On 12/16/05, Scott Plumlee [EMAIL PROTECTED] wrote: I am trying to maintain one master list of countries and the states within those countries. I'd like to keep this info in a common database, to be used by 2 or three other databases on the same host, using foreign keys to make sure that all the country/state data matches up to the master list. If I can't use foreign keys, should I just run regular updates to sync the data between duplicate tables of countries and states in each database, or is there an better method that I'm not seeing? Sorry, I didn't explain very well. I have a db I'll call Common. In there, I'd like to keep two tables, tblCountry and tblState. Then I've got another database, called Application, with a table called tblAddress. In the Application database, I'd like to reference tblCountry and tblStateProvince. When someone fills out form info that is going to be stored in tblAddress, I want the choices to be pulled from tblCountry and tblStateProvince. When I've done this inside only one database, I've always used foreign keys between the Address.State field and the tblState.State fields. My goal IS to only keep one set of country and state data for multiple databases. But I don't know if I can do that using foreign keys when things are in two different databases - it doesn't appear that I can. So my question is am I better off NOT using foreign keys but doing everything else the same or using foreign keys and having to jerryrig some sort of syncing? If your app is just selecting data and inserting it into fields. Then have it do something like: SELECT `ID`,`Stateabv` FROM `Common`.`states` ORDER BY `Stateabv`; Fields, then when the user saves the data, the ID is written to the other DB, no syncing needed. I've done something like this in another app. I'm not sure how foreign keys will work (if they will) but I *think* you can do a join on this -- tho now we are beyond the scope of my knowledge :-D -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can foreign keys reference tables in another database?
JamesDR wrote: Scott Plumlee wrote: sol beach wrote: Why in the world are you trying to keep multiple copies of the data? Why can't you just access the 'master database'? On 12/16/05, Scott Plumlee [EMAIL PROTECTED] wrote: I am trying to maintain one master list of countries and the states within those countries. I'd like to keep this info in a common database, to be used by 2 or three other databases on the same host, using foreign keys to make sure that all the country/state data matches up to the master list. If I can't use foreign keys, should I just run regular updates to sync the data between duplicate tables of countries and states in each database, or is there an better method that I'm not seeing? Sorry, I didn't explain very well. I have a db I'll call Common. In there, I'd like to keep two tables, tblCountry and tblState. Then I've got another database, called Application, with a table called tblAddress. In the Application database, I'd like to reference tblCountry and tblStateProvince. When someone fills out form info that is going to be stored in tblAddress, I want the choices to be pulled from tblCountry and tblStateProvince. When I've done this inside only one database, I've always used foreign keys between the Address.State field and the tblState.State fields. My goal IS to only keep one set of country and state data for multiple databases. But I don't know if I can do that using foreign keys when things are in two different databases - it doesn't appear that I can. So my question is am I better off NOT using foreign keys but doing everything else the same or using foreign keys and having to jerryrig some sort of syncing? If your app is just selecting data and inserting it into fields. Then have it do something like: SELECT `ID`,`Stateabv` FROM `Common`.`states` ORDER BY `Stateabv`; Fields, then when the user saves the data, the ID is written to the other DB, no syncing needed. I've done something like this in another app. I'm not sure how foreign keys will work (if they will) but I *think* you can do a join on this -- tho now we are beyond the scope of my knowledge :-D I just did a quick test on my database server and it looks like you can have a foreign key that references tables in another database. Using your example databases and tables, you can create the foreign key by specifying the database name in the REFERENCES clause like this: ALTER TABLE Application.tblAddress ADD FOREIGN KEY (State) REFERENCES *Common*.tblState (State); Eric
Re: Can foreign keys reference tables in another database?
Eric Grau wrote: JamesDR wrote: Scott Plumlee wrote: sol beach wrote: Why in the world are you trying to keep multiple copies of the data? Why can't you just access the 'master database'? On 12/16/05, Scott Plumlee [EMAIL PROTECTED] wrote: I am trying to maintain one master list of countries and the states within those countries. I'd like to keep this info in a common database, to be used by 2 or three other databases on the same host, using foreign keys to make sure that all the country/state data matches up to the master list. If I can't use foreign keys, should I just run regular updates to sync the data between duplicate tables of countries and states in each database, or is there an better method that I'm not seeing? Sorry, I didn't explain very well. I have a db I'll call Common. In there, I'd like to keep two tables, tblCountry and tblState. Then I've got another database, called Application, with a table called tblAddress. In the Application database, I'd like to reference tblCountry and tblStateProvince. When someone fills out form info that is going to be stored in tblAddress, I want the choices to be pulled from tblCountry and tblStateProvince. When I've done this inside only one database, I've always used foreign keys between the Address.State field and the tblState.State fields. My goal IS to only keep one set of country and state data for multiple databases. But I don't know if I can do that using foreign keys when things are in two different databases - it doesn't appear that I can. So my question is am I better off NOT using foreign keys but doing everything else the same or using foreign keys and having to jerryrig some sort of syncing? If your app is just selecting data and inserting it into fields. Then have it do something like: SELECT `ID`,`Stateabv` FROM `Common`.`states` ORDER BY `Stateabv`; Fields, then when the user saves the data, the ID is written to the other DB, no syncing needed. I've done something like this in another app. I'm not sure how foreign keys will work (if they will) but I *think* you can do a join on this -- tho now we are beyond the scope of my knowledge :-D I just did a quick test on my database server and it looks like you can have a foreign key that references tables in another database. Using your example databases and tables, you can create the foreign key by specifying the database name in the REFERENCES clause like this: ALTER TABLE Application.tblAddress ADD FOREIGN KEY (State) REFERENCES *Common*.tblState (State); Eric Of course without the *. I was trying to make it bold and didn't realize the mailing list would add them. Hope this helps, Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
