FK relationships between schemas in azure SQL don't work

Question

I'm having an issue with migrating my SQL 2014 database to an azure database. I'm able to get everything migrated except for any FK relationships between tables that aren't the in same schema. for example, 2 tables:

    CREATE TABLE [dbo].[User] (
        [UserGUID]          UNIQUEIDENTIFIER CONSTRAINT [DF_CoBRAUser_CoBRAUserGUID] DEFAULT (newsequentialid()) NOT NULL,
        [UserName]               NVARCHAR (400)   NOT NULL,
        [OrganizationName]       NVARCHAR (100)   NOT NULL,
        [Title]                  NVARCHAR (100)   NULL,
        [Department]             NVARCHAR (100)   NULL,
        [FirstName]              NVARCHAR (100)   NULL,
        [LastName]               NVARCHAR (100)   NULL,
        [UserImage]              VARBINARY (MAX)  NULL,
        [DateCreated]            DATETIME         NOT NULL,
        [DateLastModified]       DATETIME         NOT NULL,
        [MotherTongue]           NVARCHAR (50)    NULL,
        [LockoutEndDateUtc] DATETIME NULL,
[ClusterID]              INT              IDENTITY (1, 1) NOT NULL,
CONSTRAINT [PK_User] PRIMARY KEY NONCLUSTERED ([UserGUID] ASC)
    );
GO
CREATE UNIQUE CLUSTERED INDEX [IX_User_ClusterID] ON dbo.User ([ClusterID])
GO
create schema [CConfigD]
    GO
    CREATE TABLE [CConfigD].[Alarm] (
        [AlarmGUID]   UNIQUEIDENTIFIER CONSTRAINT [DF_Alarm_AlarmGUID] DEFAULT (newsequentialid()) NOT NULL,
        [CreatedBy]         UNIQUEIDENTIFIER NOT NULL,
        [AlarmText]         NVARCHAR (1000)  NOT NULL,
        [DateCreated]       DATETIME         NOT NULL,
        [DateLastModified]  DATETIME         NOT NULL,
        CONSTRAINT [FK_Alarm_User] FOREIGN KEY ([CreatedBy]) REFERENCES [dbo].[User] ([UserGUID])
    );

This works fine on my sql 2014 box, if i try to run a dacpac onto the azure database i get the following error:

Msg 1776, Level 16, State 0, Line 8 There are no primary or candidate keys in the referenced table 'dbo.User' that match the referencing column list in the foreign key 'FK_Alarm_User'. Msg 1750, Level 16, State 1, Line 8 Could not create constraint or index. See previous errors.

is this just not allowed and i didn't know it? Is there a setting i need to enable in order to allow this sort of foreign key?

Answer 1

The error message is self-explanatory. You need a primary key or unique index/constraint on the UserGUID column in dbo.[User].

ALTER TABLE dbo.[User] 
  ADD CONSTRAINT PK_User
  PRIMARY KEY (UserGUID);

If that is there (you edited it in later), you shouldn't get that error message.

If you are only getting this error message when deploying a dacpac, can you post that dacpac somewhere? I can't make that code fail on Azure SQL Database by running it directly, but it's possible the dacpac has creation scripts listed in the wrong order, or the constraints are separated out into a different (later) step and aren't in the right order either, or the dacpac is only deploying changes to one schema and the other one hasn't been updated.