Sql-server – FK relationships between schemas in azure SQL don’t work

azure-sql-databaseschemasql server

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?

Best Answer

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.

Related Solutions

Sql-server – How to create a partitioned table based on date

As the error message says, any partition-aligned unique index has to include the partitioning key in the index key. This requirement exists so the engine can enforce uniqueness on updates without checking every partition.

In your case, this means including OrderDate in the nonclustered index key, or having a non-aligned index. Both are potentially valid choices, depending on your circumstances. To preserve alignment, your table and index definitions would be:

CREATE TABLE dbo.Orders
(
    OrderID     integer NOT NULL,
    Name        nvarchar(20) NULL,
    OrderDate   date NOT NULL,

    CONSTRAINT PK__Orders_OrderID_OrderDate
        PRIMARY KEY NONCLUSTERED 
            (OrderID, OrderDate)
        ON PS (OrderDate)
)
ON PS (OrderDate);
GO
CREATE CLUSTERED INDEX CX__Orders_OrderDate
ON dbo.Orders (OrderDate)
ON PS (OrderDate);

Of course this changes the uniqueness that the nonclustered index enforces. Now, only the combination of OrderID and OrderDate is guaranteed to be unique. It is theoretically possible to add duplicate OrderIDs, so long as the OrderDate is different. Whether this change of semantic is acceptable to you depends on your circumstances, but it is something to be aware of.

The alternative is to have the nonclustered primary key non-aligned:

CREATE TABLE dbo.Orders
(
    OrderID     integer NOT NULL,
    Name        nvarchar(20) NULL,
    OrderDate   date NOT NULL,

    CONSTRAINT PK__Orders_OrderID
        PRIMARY KEY NONCLUSTERED 
            (OrderID)
        ON [PRIMARY]
)
ON PS (OrderDate);
GO
CREATE CLUSTERED INDEX CX__Orders_OrderDate
ON dbo.Orders (OrderDate)
ON PS (OrderDate);

This preserves the uniqueness of OrderID alone, and has some benefits with queries that compute MIN or MAX aggregates using the index, but you lose the ability to SWITCH partitions in and out without dropping the primary key and recreating it after the switching operation.

You can read more about partitioning in this section of Books Online and more on the issues of aligned and non-aligned indexes in this excellent answer by Remus Rusanu.

Sql-server – Preferred design to avoid circular/multiple update paths

The solution I've seen has been to remove the foreign key constraint, and to maintain relational integrity via triggers.

On the foreign (or "child") table, you need INSERT and UPDATE triggers. if the "foreign key" column(s) are inserted/updated, then you have to make sure the value(s) exists in the primary key of the "parent" table.

On the "parent" table, on UPDATE of the primary key column(s), you have to locate all rows in any "child" tables (FarmEquipment and Fields would both be "children" of Farmers) with the old value, and update them to the new value.

On the "parent" table, on DELETE, you have to check the "child" tables to see if any rows use the primary key(s) in question, and delete them all.

Having worked with a vendor-supplied system that worked this way, I can't recommend it.

As noted above, you must maintain three chunks of code for each foreign key relationship, across a minimum of two triggers (let's leave self-referential situations out of the discussion, for the moment at least).
A "parent" table that tied to multiple "child" tables would need a check for each "child" table. While the code should be very similar for each "child" table, that can actually make modifications more difficult, as it would be easy to make a change meant to affect FarmEquipment to Fields by mistake. (In the real-world example I mentioned, there was at least one parent table with at least 20 "FK" relationships - sometimes having multiple links in the same child table, to different fields.
Documentation of the relationship is no longer built-in. While you can't (to the best of my knowledge) right-click a table and find all foreign key relationships that point to it, it's easy to find a query to retrieve this information from SQL Server's structural tables. When the relationship is maintained via triggers, you pretty much have to go through the triggers manually to identify all the relationships, and the precise definition (the "parent" table's DELETE trigger is where you can determine if the relationship is meant to be CASCADE, NO ACTION, SET NULL, or SET DEFAULT - with foreign key constraints, this can be seen from the "child" table).

There are workarounds (the trigger code does exist in a queryable form), but they require that the developers follow strict rules regarding naming conventions, the construction of the query, and possibly even the formatting of the code. Even if one person is maintaining the code, and is using a template to set everything up, keeping everything exactly so as changes are made and bugs are found and fixed becomes much more difficult.

The system I worked with did work, with this method, for more than 10 years; they'd started their app in an environment that didn't have effective foreign keys. But even they had gotten to the point where they were using foreign keys for new tables, and starting to put them in place on existing tables where possible. It's just that working with it wasn't easy.

I suspect this wouldn't meet your criteria; however, as it is a viable solution, I thought it was at least worth covering.

Best Answer

Related Solutions

Sql-server – How to create a partitioned table based on date

Sql-server – Preferred design to avoid circular/multiple update paths

Related Question