July 23, 2008 at 7:39 am
i am trying to update the primary key column values of a table. along with this table many other tables need to be updated because of several constraints.
the following table need to be updated with the affected column. foreign key column name is not same as primary key column name
3 tables
table1(column1(pk),column2,coumn3,...)
table2(coumn4(pk),column5(fk references table1(column1),...)
table3(coumn8(pk),column9(fk references table1(column1),column10(fk references table1(column1),..)
i am trying to do this using a stored procedure with 3 steps.
1.remove each constraint
2.perform update
3. add constraints
code i wrote:
ALTER PROCEDURE updateproc
@Prevvalue varchar(15),
@oldvalue varchar(15)
as
SET NOCOUNT ON
DECLARE @raiseerror INT
BEGIN transaction
ALTER TABLE table2
DROP CONSTRAINT FK_table2_table1
ALTER TABLE table3
DROP CONSTRAINT FK_table31_table1
ALTER TABLE table3
DROP CONSTRAINT FK_table32_table1
ALTER TABLE table1
DROP CONSTRAINT PK_table1
UPDATE table1
SET column1 = @Newvalue
where coulmn1=@oldvalue
UPDATE table2
SET column5 = @Newvalue
where coulmn5=@oldvalue
UPDATE table3
SET column9 = @Newvalue
where coulmn9=@oldvalue
UPDATE table3
SET column10= @Newvalue
where coulmn10=@oldvalue
ALTER TABLE table1
ADD CONSTRAINT PK_table1
PRIMARY KEY NONCLUSTERED
(
column1
) WITH FILLFACTOR = 90 ON PRIMARY
ALTER TABLE table2
ADD CONSTRAINT FK_table2_table1
FOREIGN KEY(column5)
REFERENCES table1(column1)
ALTER TABLE table3
ADD CONSTRAINT FK_table31_table1
FOREIGN KEY(column9)
REFERENCES table1(column1)
ALTER TABLE table3
ADD CONSTRAINT FK_table32_table1
FOREIGN KEY(column10)
REFERENCES table1(column1)
if(@@error<>0)
Begin
rollback transaction
set @raiseerror=1
End
else
Begin
commit transaction
set @raiseerror=0
End
Is this the wright way to code.?
when i tried to run this an error has occurred
error: ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint.
July 23, 2008 at 7:56 am
You do not have to drop the Primary Key constraint on table1 as you can update values in a Primary Key Column. Have you attempted to isolate the alter table that is causing the error? Start with table1 and table2 then add the new ones.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply