0

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?

  • 1
    A foreign key requires a primary key or a unique constraint in the other table. dbfiddle.uk/… – McNets Jan 13 at 20:39
  • sorry, i was abbreviated my actual table and missed a spot. i actually do assign the primary key in there, see edited – Phil Jan 13 at 20:54
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.

  • yeah, i apologize, i do actually add the key but forgot to include it in my example – Phil Jan 13 at 20:55
  • Can you post the dacpac somewhere? I can't make that code fail on Azure SQL Database 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. – Aaron Bertrand Jan 13 at 20:56
  • Thanks for your response, but i'm trying to figure out how to make a smaller version of the tables that show the issue, but you're right it works for me. I am using a dacpac, but i'm also using schema compare on my sql project and just running the script it generates from there. doesn't seem to be an ordering issue (schema is already created). I'll keep investigating though. looks like it might not be any azure specific thing like i thought then. – Phil Jan 13 at 21:05
  • I don't mean the CREATE SCHEMA specifically, I mean changes to the schema in general - tables, constraints, etc. Sorry to be ambiguous. I would check what's there before you deploy the script, the order of the scripts in the dacpac, and the output of the schema compare. Not all schema compares are equal. – Aaron Bertrand Jan 13 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.