Violation of PRIMARY KEY constraint error when upgrading Data-tier Application

Question

I am trying to standardise the schema of my database using a data-tier application.

To do this, I have an existing Data-tier application that I want to apply to my database.

In attempting to Upgrade the Data-Tier application, I get the following error message:

Error SQL72014: .Net SqlClient Data Provider: Msg 2627, Level 14, State 1, Line 19 Violation of PRIMARY KEY constraint 'tmp_ms_xx_constraint_PK_ResidencyAgent'. Cannot insert duplicate key in object 'dbo.tmp_ms_xx_ResidencyAgent'. The duplicate key value is (1).
Error SQL72045: Script execution error.  The executed script:
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_ResidencyAgent] (
    [ResidenceID] INT      NOT NULL,
    [AgentID]     INT      NOT NULL,
    [CreateDate]  DATETIME NOT NULL,
    [IsDefault]   BIT      NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK_ResidencyAgent] PRIMARY KEY CLUSTERED ([AgentID] ASC)
);

IF EXISTS (SELECT TOP 1 1 FROM   [dbo].[ResidencyAgent])
    BEGIN

        INSERT INTO [dbo].[tmp_ms_xx_ResidencyAgent] ([AgentID], [ResidenceID], [IsDefault], [CreateDate])
        SELECT   [AgentID],
                 [ResidenceID],
                 [IsDefault],
                 [CreateDate]
        FROM     [dbo].[ResidencyAgent]
        ORDER BY [AgentID] ASC;

    END

DROP TABLE [dbo].[ResidencyAgent];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_ResidencyAgent]', N'ResidencyAgent';

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_ResidencyAgent]', N'PK_ResidencyAgent'

I have tried running this query against the database and the error message is

Msg 2627, Level 14, State 1, Line 19
Violation of PRIMARY KEY constraint 'tmp_ms_xx_constraint_PK_ResidencyAgent'. Cannot insert duplicate key in object 'dbo.tmp_ms_xx_ResidencyAgent'. The duplicate key value is (1).

The data that is currently in the [dbo].[ResidencyAgent] table is:

+-----------+-------------+-----------+-------------------------+
| AgentID   | ResidenceID | IsDefault | CreateDate              |
+-----------+-------------+-----------+-------------------------+
| 1         | 1           | 1         | 2011-10-06 13:28:42.110 |
| 1         | 2           | 0         | 2011-10-06 13:28:43.483 |
| 1         | 28          | 0         | 2017-09-01 14:53:08.750 |
| 6         | 1           | 1         | 2016-02-10 14:21:57.150 |
| 14        | 1           | 1         | 2017-08-07 16:37:13.020 |
+-----------+-------------+-----------+-------------------------+

The reason that the data-tier application has a problem executing the script is because the table contains a composite key and therefore the temporary table that it creates should be defined as follows:

CREATE TABLE [dbo].[tmp_ms_xx_ResidencyAgent](
    [ResidenceID] [int] NOT NULL,
    [AgentID] [int] NOT NULL,
    [IsDefault] [bit] NULL,
    [CreateDate] [datetime] NOT NULL,
 CONSTRAINT [tmp_ms_xx_PK_ResidencyAgent] PRIMARY KEY CLUSTERED 
(
    [ResidenceID] ASC,
    [AgentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Has anyone else encountered something similar? If so, how did they resolve it?

Is there some way in which I can make the data tier application 'aware' of this - is there an option that I can set? Can I edit the dacpac that I am using to upgrade the data tier application to get round this issue?

Thanks,

Sean