'Updating PK and FKs

I have a specific use case where i am not sure of the impact.

I have about 100 databases with the same datamodel (generated by the same code base with ASP.NET and EF6).

When we have a new customer/tenant, we copy a setup database (with a lot of preexisting data in it). We have one record Tenant with a specific ID in the setup database. We change Tenant.Name and continue setup in the new environment.

Now, after many years, we have many databases for different Tenants, but they all have the same Tenant.Id (as we copied the DB).

I know we can disable constraints and update Tenant.Id (=PK) and then loop over all tables with FKs and update it there as well to make them unique.

But, i am sure there are drawbacks. Im pretty sure statistics and indexes will/should be rebuilt, but are there any other considerations? Of course the database shoudl be offline and no real traffic should be able to reach it during these fixings.

I know there are password hashes generated while using this Tenant.Id as a salt, which is bad, but a problem we can fix. Dont ask lol.

Regards Tim

asp.netsql-serverentity-frameworksql-server-2019


Solution 1:[1]

You do not need to disable PK constraints to UPDATE a PK if this PK is not an IDENTITY. You can also use the CASCADE mode for the referential integrity of the FK to synchronise the PK updates.

But a problem is the use of IDENTITY, because you cannot UPDATE a column that is involved with an identity property...

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 SQLpro

Related Questions