How to create a foreign key that points to a table in different database

Question

How to create a foreign key that points to a table in different database

aks-156144

SSChasing Mays

Points: 659
More actions
March 28, 2006 at 9:04 am

#171028

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
SORRY I HAD PUT THIS QUESTION EARLIER UNDER NOTIFICATION SERVICES AND NOW I AM MOVING IT UNDER "ADMINISTRATION", MY APOLOGIZES FOR THE DUPLICATION.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

cross database foreign keys are not possible; the best you can do is create a trigger that performs the logic to make sure the relationship exists that you are trying to enforce.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

Or if you know that this table is updated only by your application, you can put this logic on the application level.

Regards,Yelena Varsha

DanKennedy Hall of Fame Points: 3736 More actions · Answer 3

Don't know if you considered this but it is also possible to create a CHECK CONSTRAINT, referencing a UDF which performs the cross database check hence enforcing DRI.

Dan
www.firstcs.co.uk

aks-156144 SSChasing Mays Points: 659 More actions · Answer 4

Thank you all for all the suggestion.

Although I mentioned only few table names as a sample for this purpose but I have more than 450 of these reference tables (that are referenced by FK from various tables) that we were thinking of moving to a seperate database and that is why creating triggers OR UDFs don't seem that feasible.

Actually the main purpose for doing this is, our application team wants to create a standrd framework to use these reference so that they can use that in various applications.

If any of you have ever heard of anyone doing this or any other suggestion please let me know.

Thanks much

Lowell SSC Guru Points: 323518 More actions · Answer 5

well i could see that you can add viewnames with the identical table names for each of the 450 lookup tables, and use them in your application, but that would leave the foreign keys missing, since you can't FK to a view.

for example

CREATE VIEW zipcodes as

select * from master.dbo.zipcodes

if all your references are added from the application, which assumes the table/view exists in the current database, and selects from that, i'd bet you'd be ok.

would that idea help?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

steve block SSCertifiable Points: 7698 More actions · Answer 6

If the databases have not been created yet, try putting the shared tables in the model database and then when a new database is created it will start out with the tables that have been set up. There will be some problems with maintaining the multiple the tables in the multiple databases but I'm sure a script could be written to copy out the data from model to all the databases.

Steve

aks-156144 SSChasing Mays Points: 659 More actions · Answer 7

Thanks for all the suggestion. we might have to reconsider about the idea of having a seperate lookup database. As suggested creating views would resolve the problem partially but the biggest hurdle remains the FKs and these table keys are used very heavily as FKs.