CODEWITHSUNDEEP
sql-serverssmsdatabase-projectdacpac
Simon Green
Simon Green

Reputation: 1161

Sql Database Project that extends existing table

I have a 'Core' VS Database project, that includes a table called [dbo].[MyTable], with a number of columns on it. I reuse this project in a number of solutions, where I also include an application-specific Database project that references the core database project, and adds more tables and FKs from those tables onto the tables in the core project.

Is it possible to 'build on' tables defined in the core db project, that is to add more columns to an existing table, so that those columns are only defined for one application-specific db project and not any of the others that reuse the same core db project?

Edit: I have just found the same question posted over 6 years ago. Apparently, back then the answer was 'no', but maybe it is possible in newer versions of VS?

https://social.msdn.microsoft.com/Forums/en-US/3583a5ea-d653-4268-a1d0-f58788c0cebf/partial-project-add-extra-column-to-table?forum=vstsdb

Upvotes: 1

Views: 1470

Answers (2)

Brad D
Brad D

Reputation: 762

You can add columns in a post-deploy script, but the unfortunate thing is that you cannot reference these columns else where in the project. So in my post-deploy script for the specific instance of the project I would check for the existence of the table and of the column, and if the column does not exist I would add it. However that column added by script won't be added to the data model for you to reference elsewhere via another stored procedure or view.

A possible workaround might be to leave your core table as is, in the core project. In the extension project create a table, with the appropriate foreign keys and constraints back to the core table, along with the additional column(s), effectively a 1:1 relationship. And if necessary virtualize the two tables with a VIEW, that contains the core columns, plus the additional columns from the extension table.

If the core is standardized across all instances, you want to keep the core that way. think of those extensions as part of the rules of separation of responsibility. if the core ever changes, it should only impact the core, and if the extensions ever need to change, only those should be changed.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

This is too long for a comment.

If I understand your question correctly, you are looking for something like table inheritance. This is supported by Postgres (see the documentation here). And it seems to be exactly what you describe.

If so, then SQL Server does not support it directly. My advice would be to have the core table and then for each application have another table with the same clustered index for the additional columns.

Upvotes: 0

Related Questions