Error on removed index creating a database using a VS 2017 database project

[DavidThi808]

I have a Database project I created in VS 2017. I've changed it substantially including moving columns to different tables, etc.

I then deleted the bin & obj folders. I also went to C:\Users\david\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\ProjectsV13, killed sqlserver.exe in the running processes, and then deleted all files in that folder.

And I am still getting the following when I build:

------ Build started: Project: Database1, Configuration: Debug Any CPU ------
Loading project references...
Loading project files...
Building the project model and resolving object interdependencies...
Validating the project model...
Writing model to C:\git\Store\Database1\obj\Debug\Model.xml...
Database1 -> C:\git\Store\Database1\bin\Debug\Database1.dll
Database1 -> C:\git\Store\Database1\bin\Debug\Database1.dacpac
------ Deploy started: Project: Database1, Configuration: Debug Any CPU ------
C:\git\Store\Database1\bin\Debug\Database1.sql: Warning: SQL72023: The database containment option has been changed to None. This may result in deployment failure if the state of the database is not compliant with this containment level.
C:\git\Store\Database1\bin\Debug\Database1.sql: Warning: SQL72015: The column [dbo].[Organization].[BillingId] is being dropped, data loss could occur.
C:\git\Store\Database1\bin\Debug\Database1.sql: Warning: SQL72015: The column [dbo].[Person].[CompanyName] is being dropped, data loss could occur.
C:\git\Store\Database1\bin\Debug\Database1.sql: Warning: SQL72015: The column [dbo].[Person].[IsBilling] is being dropped, data loss could occur.
C:\git\Store\Database1\bin\Debug\Database1.sql: Warning: SQL72015: The column [dbo].[Person].[IsEnabled] is being dropped, data loss could occur.
C:\git\Store\Database1\bin\Debug\Database1.sql: Warning: SQL72015: The column [dbo].[Subscription].[BillingErrorDate] is being dropped, data loss could occur.
C:\git\Store\Database1\bin\Debug\Database1.sql: Warning: SQL72015: The column [dbo].[Subscription].[BillingMode] is being dropped, data loss could occur.
C:\git\Store\Database1\bin\Debug\Database1.sql: Warning: SQL72015: The column [dbo].[Subscription].[BillingStatus] is being dropped, data loss could occur.
C:\git\Store\Database1\bin\Debug\Database1.sql: Warning: SQL72015: The column [dbo].[Subscription].[ChargeToken] is being dropped, data loss could occur.
C:\git\Store\Database1\bin\Debug\Database1.sql: Warning: SQL72015: The column [dbo].[Subscription].[DeactivateDate] is being dropped, data loss could occur.
C:\git\Store\Database1\bin\Debug\Database1.sql: Warning: SQL72015: The column [dbo].[Subscription].[DeleteDate] is being dropped, data loss could occur.
C:\git\Store\Database1\bin\Debug\Database1.sql: Warning: SQL72015: The column [dbo].[Subscription].[Discount] is being dropped, data loss could occur.
C:\git\Store\Database1\bin\Debug\Database1.sql: Warning: SQL72015: The column [dbo].[Subscription].[NextBaseBillingDate] is being dropped, data loss could occur.
C:\git\Store\Database1\bin\Debug\Database1.sql: Warning: SQL72015: The column [dbo].[Subscription].[NextOverageBillingDate] is being dropped, data loss could occur.
C:\git\Store\Database1\bin\Debug\Database1.sql: Warning: SQL72015: The column [dbo].[Subscription].[ShutoffDate] is being dropped, data loss could occur.
C:\git\Store\Database1\bin\Debug\Database1.sql: Error: SQL72031: This deployment may encounter errors during execution because changes to [dbo].[Subscription].[ApiKey1] are blocked by [dbo].[Subscription].[IX_Subscription_ApiKey]'s dependency in the target database.
C:\git\Store\Database1\bin\Debug\Database1.sql: Error: Deploy72002: Plan verification encountered errors; deployment cannot continue.
Done building project "Database1.sqlproj" -- FAILED.

Build FAILED.
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 0 succeeded, 1 failed, 0 skipped ==========

There no longer is an IX_Subscription_ApiKey (it is replaced with IX_Subscription_ApiKey1).

The subscription.sql file is (the '123' and '4546' is just there to try and get this to run):

CREATE TABLE [dbo].[Subscription] (
[SubscriptionId] BIGINT IDENTITY (1, 1) NOT NULL,
[OrganizationId] BIGINT NOT NULL,
[SubscriptionName] NVARCHAR (255) NOT NULL,
[ApiKey1] CHAR (36) NOT NULL DEFAULT '123' ,
[ApiKey2] CHAR (36) NOT NULL DEFAULT '4546' ,
[IsEnabled] BIT CONSTRAINT [DF_Subscription_Enabled] DEFAULT ((1)) NOT NULL,
[IsActive] BIT CONSTRAINT [DF_Subscription_Active] DEFAULT ((1)) NOT NULL,
[AccruedRtusThisMonth] BIGINT CONSTRAINT [DF_Subscription_PendingRtus] DEFAULT ((0)) NOT NULL,
[BilledRtusThisMonth] BIGINT CONSTRAINT [DF_Subscription_PaidRtus] DEFAULT ((0)) NOT NULL,
[OverageChargesThisMonth] MONEY CONSTRAINT [DF_Subscription_OverageChargesThisMonth] DEFAULT ((0)) NOT NULL,
[PricingMode] TINYINT NOT NULL,
[PricingPlanId] BIGINT NULL,
[MaxAdditionalMonthlyCharge] MONEY NOT NULL,
[XtraAutoTags] INT CONSTRAINT [DF_Subscription_XtraAutoTags_1] DEFAULT ((0)) NOT NULL,
[EndFreeUse] SMALLDATETIME CONSTRAINT [DF_Subscription_EndFreeUse_1] DEFAULT (getutcdate()) NOT NULL,
[Created] SMALLDATETIME CONSTRAINT [DF_Subscription_CreatedDate] DEFAULT (getutcdate()) NOT NULL,
[TS] ROWVERSION NOT NULL,
CONSTRAINT [PK_Subscription] PRIMARY KEY CLUSTERED ([SubscriptionId] ASC),
CONSTRAINT [FK_Subscription_PricingPlan] FOREIGN KEY ([PricingPlanId]) REFERENCES [dbo].[PricingPlan] ([PricingPlanId]),
CONSTRAINT [FK_Subscription_Organization] FOREIGN KEY ([OrganizationId]) REFERENCES [dbo].[Organization] ([OrganizationId])
);

GO
CREATE NONCLUSTERED INDEX [IX_Subscription_OrganizationId]
ON [dbo].[Subscription] ([OrganizationId] ASC);


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Subscription_ApiKey1]
ON [dbo].[Subscription]([ApiKey1] ASC);


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Subscription_ApiKey2]
ON [dbo].[Subscription]([ApiKey2] ASC);


Why is it unhappy?

thanks - dave









What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

Continue reading...