October 10, 2016 at 1:27 am
i am facing a problem. i've two tables. one is Master table and other is Details table. there are many rows in details table corresponding to one Master table row. Now i've to change my primary key in Master table. so how can i do this ?
Like if i want to change primary key to this
PK
0001
0002
0003
then i want to change this foreign key in Details table as well without data losing
October 10, 2016 at 2:55 am
You'll need to remove any foreign key constraint on the detail table, add a new column with the new primary key on the master table, then update the foreign key on the detail table and as a last step, drop the old primary key and recreate the foreign key constraint.
If you have trouble understanding these steps, don't hesitate to ask for clarification.
-- Gianluca Sartori
October 10, 2016 at 6:03 am
i have done it in other way
i checked UPDATE RULE to CASCADE in relationships in MASTER TABLE
by CASCADE property set.. when we change primary key in MASTER TABLE all references will be changed automatically !
below is the code i used
DECLARE @Temp TABLE (TempID INT IDENTITY(1,1), ID INT)
INSERT INTO @Temp (ID)
SELECT g.Id_Num FROM MASTER TABLE AS g
declare @Total int = @@ROWCOUNT
DECLARE @MyID VARCHAR(50) = 2016000001
DECLARE @Count INT = 1
DECLARE @ID INT
PRINT @Total
WHILE @Count <= @Total
BEGIN
SET @ID = (SELECT ID FROM @Temp WHERE TempID = @Count)
-- Update Master Table
UPDATE MASTER TABLE SET Id_Num = @MyID WHERE Id_Num = @ID
SET @MyID = '2016000000' + CAST(SUBSTRING(@MyID,5,9) AS INT ) + 1
SET @Count =@Count +1
end
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply