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?