CODEWITHSUNDEEP
sql-serverimportsql-server-2008-r2ddlsql-import-wizard
ridermansb
ridermansb

Reputation: 11059

Change ID of row and reflect this change to all related tables

Old version

I have a Person table and the table Company.

both tables have a column Id (Identity)

Table Company have Ids of 1 to 165 In the table Person have Ids 1 until 2029

New Version

In the new version of the system, was created a table Entity. This table contains the records of the Companies and People

The Company and Person tables will be maintained, referring to the Entity table.
The Id in table Entity will be the same in Company or Person table

Question

Both tables have multiple relationships with other tables. Table Entity (as well as others) has a column ID (identity). The problem is that the Id were repeated when the two tables together (It was to be expected).

How to import without losing relationships?

Attempts

I thought of changing the value of Ids in Company table, starts from 2030. Thus the Ids would not duplicate when joining the two tables.

But this creates another questions.

  1. How to do this without losing existing relationships?
  2. How to change the Id of a row in the table and this is reflected in all tables which it relates?

I would like to do this using only DDL (SQL Server)

Upvotes: 1

Views: 2657

Answers (2)

Greenstone Walker
Greenstone Walker

Reputation: 1150

I thought of changing the value of Ids in Company table, starts from 2030. Thus the Ids would not duplicate when joining the two tables.

Create foreign key constraints on the Person table to all related tables (or alter the existing foreign key constraints) with ON UPDATE CASCADE. Then update the Person table and change the values if the id columns - these changes will cascade to the related tables.

To stop further problems, maybe change the identity columns in Person and Company to something like identity( 1000, 3 ) and identity (1001, 3) respectively.

However, I think the best idea is to have a different EntityID column in the Entity table, unrelated to PersonID and CompanyID. The Entity table would also have a column called AltEntityID or BusinessKey that contains the id from the other table and that does not have a unique constraint or a foreign key constraint.

Upvotes: 1

Serg
Serg

Reputation: 2427

And if you make small modification to your attempt - add new column, say newId, to Company and to Person to manage relation with Entity and leave id columns as is. Why this is the simpliest way? Because new columns shouldnot be identity columns, from one side. From the other side, you can leave all logic of relating other tables with Company and Person intact.

Upvotes: 0

Related Questions