We have a product that runs off SQL databases.
Until recently, each of our clients had their own fileserver running SQL Server, however we have now started hosting on Azure for some existing and some new clients.
Attempting to roll out the latest version of the software to our clients, we’ve run into an issue with some (but not all!) of the Azure-hosted databases.
The update required adding some new tables, which had Foreign Keys to some existing tables. This worked fine on all the SQL Server hosted databases, but on some of the Azure hosted databases we got the following error:-
There are no primary or candidate keys in the referenced table ‘TableName’ that match the referencing column list in the foreign key ‘FK_Name’
But there were definitely valid PKs, all the databases had the same schema, and only some had issues. Those that had issues also reported the issue against different tables.
The solution turned out to be that the indexes needed rebuilding on some of the tables:-
ALTER INDEX ALL ON TableName REBUILD;
In some cases, more than one index needed to be rebuild.
Once that was done, the scripts to create the new tables ran without issue.
All of the Azure databases are quite new, and most of them barely have any data in them. Half a dozen records in the tables affected, in some cases, so not a case of indexes getting too big, or too fragmented.
The tables themselves worked fine, before and after the indexes were rebuilt, and the software didn’t throw any errors.
The only obvious issue related to this is being unable to reference the PKs with FKs.
However, we’ve obviously concerned that there is some underlying issue with Azure and indexes, and that we could face worse problems down the line.
Has anyone else experienced this issue?
Is there something we need to be doing on Azure to stop indexes breaking?
I ran overnight jobs on all the Azure databases to rebuild all indexes, so hopefully we’re now good, but I’d still like to know what happened, and why, and how to stop it happening again.
Different indexes, on different tables, on different Azure databases, have broken somehow in the last few weeks (newest DB) to months (oldest DB), and we’d kinda like to know why!