March 28, 2006 at 8:40 am
We are planning to store all reference values (say for example a list of all states, or zip codes or countries etc) in one database (in different tables) so that all other databases (on the same server) can point to the same referecne values.
My question is, is there a way where we can have foreign keys pointing to another database? (for example if the reference data is in a database called "ref" and table is in "Pub" database on which we want to put the FK)
One way that I can think of now is to create views for all the reference tables in the local database (like Pub) and then create a trigger (for insert, update etc) to make sure that the value inserted does belong to reference list otherwise rollback it. But I don't think this is the best way because in this we are doing the actual insert/update before checking for the values.
Any help/suggestion is appreciated.
Thanks
March 28, 2006 at 12:36 pm
I see you got a response in another forum: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=269019
Please don't cross post.
Greg
Greg
March 29, 2006 at 10:53 am
Well, am not sure if foreign key references another table in a different database but as a workaround, you may try the linked server options. By linking 2 DB server, you will be able to cross query or apply reference keys through it.
Worth checking the BOL....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply