0

In Visual Studio 2013, I have setup two database projects in the same solution (DatabaseA and DatabaseB), with DatabaseA having a "database reference" to DatabaseB.

Database solution

This all looks great, so I create TableB in DatabaseB and then a view in DatabaseA to get the data in TableB.

CREATE VIEW [dbo].[vDatabaseB]
    AS SELECT Id FROM [$(DatabaseB)].dbo.TableB

Works perfectly. I now create a TableA in DatabaseA which will have a foreign key constraint to TableB. 

CREATE TABLE [dbo].[TableA]
(
    [Id] INT NOT NULL PRIMARY KEY, 
    [TableBID] INT NOT NULL
    CONSTRAINT FK_TableA_TableBID FOREIGN KEY (TableBId) REFERENCES [$(DatabaseB)].dbo.TableB(Id) ON DELETE CASCADE,

Unfortunately, this just doesn't build:

Error   SQL71501: Foreign Key: [dbo].[FK_TableA_TableBID] has an unresolved reference to Column [dbo].[TableB].[Id].    DatabaseA   TableA.sql  5

How do I get this table constraint to work? I also tested this in Visual Studio 2015 (preview) with the same results.

Sam
  • 669
  • 1
  • 13
  • 26

1 Answers1

3

If the projects get deployed to the same database:

When you add the reference, choose "Same database" under "Database location".

When you do that you will not need to use the database name variable and it should just work.

When you deploy you will need to make sure you have include composite objects if it doesn't exist or you choose to drop all objects.

If you actually have two different databases then sql doesn't support cross database foreign keys, you would need to implement a trigger (yuck) to support it

Ed Elliott
  • 6,666
  • 17
  • 32
  • unfortunately, these are two separate database projects (that are deployed to two separate databases on the sql server). Do you have a reference/link to the cross database foreign keys restriction? – Sam Jan 06 '15 at 22:39
  • 1
    See http://technet.microsoft.com/en-us/library/ms175464%28v=sql.105%29.aspx - the Note there explains that you can only create FKs for tables in the same database. (I have not tried this against a synonym of a table that resides in another DB.) – Peter Schott Jan 06 '15 at 23:00
  • Here is more detail and an example trigger:http://stackoverflow.com/questions/4452132/add-foreign-key-relationship-between-two-databases – Ed Elliott Jan 07 '15 at 07:36
  • Thanks for that link Peter. MSDN is so vague about this it made me laugh, but another DBA I work with confirmed the same thing this morning. Our usage of multiple databases still makes sense, so we are just not going to have this constraint. – Sam Jan 07 '15 at 13:37
  • Any idea Peter how I can mark your comment as the answer (you can't created FK's across databases and it's generally a bad idea) – Sam May 11 '15 at 22:17