In Visual Studio 2008 Database edition one can use "partial projects" to allow a database to be separated into multiple projects for deployment and maintainability. I have been looking into doing this with our project but hit the following snag:
If you have project that defines some base tables, and then you have a different project that defines a new set of tables that have constraints pointing to the tables in the first project, DBPro seems unable to map this relationship. The specific error is:
"CONSTRAINT has an unresolved reference to Table foo" (where foo is in the original DB).
A more concrete example if you'd like to duplicate for yourself the scenario:
Create a Project called BaseDB.
Define in BaseDB a table called Users with the following DDL:
CREATE TABLE [dbo].[Users] ( UserID INT IDENTITY(1,1) PRIMARY KEY, UserName NVARCHAR(20) NOT NULL )
Export BaseDB as a partial project adding the _BaseDB.files file to your project.
Create a Project in the same solution called DerivedDB
Use the Import Partial Project to point to BaseDB, confirming if you like that there's a stub reference in your import files pointing to the Users table in BaseDB.
Define a table in DerivedDB called PowerUsers with the following DDL:
CREATE TABLE [dbo].[PowerUsers] ( PowerUserID INT IDENTITY(1,1) PRIMARY KEY, UserID INT NOT NULL )
If you do a "Build" at this point, everything works.
Add a FOREIGN KEY CONSTRAINT in the DerivedDB project from PowerUsers to Users with the following DDL:
ALTER TABLE [dbo].[PowerUsers] ADD CONSTRAINT [PowerUsers_Users_FK] FOREIGN KEY (UserID) REFERENCES [dbo].[Users] (UserID)
Performing the above steps should allow you to see the error I'm talking about.
Questions:
Is there a way to fix constraint references across database projects?
If not, should partial projects then be reserved for series of stored procedures and the base project reserved for all DDL of base tables and constraints?