locked
Foreign Key on a view RRS feed

  • Question

  • Hi , 

    I have a server setup for datawarehouse and multiple databases for each department . For example , sales has a separate database for the datawarehouse , finance etc . As part of the DB design that i have , there are common dimensions across all these databases . We wanted to save some space and trying to setup a separate database for the common dimensions . Our initial plan was to remove those common dimension tables from the databases and move them over to the dimension DB . then create views on the Databases connecting to the Dimension DB . and should be able to create a relationship from the view to the Fact table and we are not able to create a foreign key . Any ideas or solutions ?

    Regards, 

    Savio 


    Regards, Savio

    Monday, March 5, 2018 7:22 PM

Answers

  • You cannot create FKs across databases.

    The best solution is to use a trigger to verify the key.

    https://stackoverflow.com/questions/4452132/add-foreign-key-relationship-between-two-databases

    Monday, March 5, 2018 7:59 PM
    Answerer
  • Rather than separate databases, have you considered separate schemas instead? This will allow you to reuse the common dimension tables in place rather than worrying about Views etc.

    You can easily maintain security across schemas in the same manner as databases.


    Martin Cairney SQL Server MVP

    Monday, March 5, 2018 8:04 PM

All replies