Wednesday, June 21, 2017

Update PK column in parent tables in T-SQL

drop table p;
drop table c;

create table p (id integer, name varchar(100) 
constraint p_pk primary key (id));
create table c (id integer, name varchar(100),pid 
integer constraint c_pk primary key (id) 
constraint c_p_fk foreign key (pid) references p(id));

insert into p values (1,'a'),(2,'b');
insert into c values (1,'a',1),(2,'b',2);

begin transaction
update p set id=3 where id=1;
update c set pid=3 where pid=1;
commit;

/************
Msg 547, Level 16, State 0, Line 14
The UPDATE statement conflicted with the REFERENCE constraint "c_p_fk". The conflict occurred in database "HRDB", table "dbo.c", column 'pid'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 15
The UPDATE statement conflicted with the FOREIGN KEY constraint "c_p_fk". The conflict occurred in database "HRDB", table "dbo.p", column 'id'.
The statement has been terminated.
************/

ALTER TABLE p NOCHECK CONSTRAINT ALL;
ALTER TABLE c NOCHECK CONSTRAINT ALL;

begin transaction
update p set id=3 where id=1;
update c set pid=3 where pid=1;
commit;

ALTER TABLE p WITH CHECK CHECK CONSTRAINT ALL;
ALTER TABLE c WITH CHECK CHECK CONSTRAINT ALL;

select * from p;
select * from c;