-1

I have a table as follows:

create table temp_cte1(id int primary key,name nvarchar(max))

And another table as follows:

create table temo_cte2(id int,name nvarchar(max)),constraint fk_id foreign key(id) references temp_cte1(id)

Let us say I have the following values in both table:

insert into temp_cte1 values(1,'Vinay'),(2,'Afzal'),(3,'Yogesh'),(4,'Shashank')

insert into temp_cte2 values(1,'Arya'),(2,'Hussain'),(3,'Kwatra'),(4,'Sharma')

Now, as long as I use the cte to update the 'name' column of any table, it works fine. But when I am trying to update the id, i am getting an error as the foreign key has been violated.

Query that is working fine:

with cte(id,FirstName,LastName) as(select t1.id,t1.name FirstName,t2.name LastName from temp_cte1 t1 inner join temp_cte2 t2 on t1.id=t2.id)
update cte set LastName='Arya' where id=1

But what I need.. to do is something like this:

with cte(id,FirstName,LastName) as(select t1.id,t1.name FirstName,t2.name LastName from temp_cte1 t1 inner join temp_cte2 t2 on t1.id=t2.id)
update cte set id=1222 where FirstName='Vinay'

Any help with this??? Thanks in advance.

PravinS
  • 2,640
  • 3
  • 21
  • 25
vstandsforvinay
  • 138
  • 1
  • 11
  • 1
    May I ask _why_ you need to update the primary key? It sounds like a less good idea, _especially_ with foreign keys referencing it. – Joachim Isaksson Mar 28 '14 at 05:48
  • whatever the idea might sound, the problem is that seniors at my office wants this. – vstandsforvinay Mar 28 '14 at 05:49
  • 1
    [This answer](http://stackoverflow.com/a/2499328/477878) handles that case, however I can't stress enough that _changing primary keys is a very bad idea_. – Joachim Isaksson Mar 28 '14 at 05:54
  • okay so is there an option that when i am trying to change the foreign key say id column the primary key gets changed.???? – vstandsforvinay Mar 28 '14 at 06:08
  • 1
    I sincerely doubt there is since it's not meant to be changed, but the only thing I can say for sure is that there is no way that I know of. – Joachim Isaksson Mar 28 '14 at 06:20
  • Okay, I guess I have what I needed. First, I set the insert update specification of the temp_cte1 table as "Cascade" on update. Then when I use the following query, i get what I need With cte as(select t1.id,t1.name FirstName,t2.name LastName from temp_cte1 t1 inner join temp_cte2 t2 on t1.id=t2.id) update t1 set t1.id=1222 from temp_cte1 t1 inner join cte on t1.id=cte.id where cte.FirstName='Vinay' The query is working fine for me. Thanks for your response. And pardon me in case i wasted your precious time. – vstandsforvinay Mar 28 '14 at 06:53
  • if you are going for "Cascade" on update a simple update query will do the job – Anto Raja Prakash Mar 28 '14 at 07:15

1 Answers1

1

I think that should work for you:

ALTER TABLE temp_cte2 NOCHECK CONSTRAINT ALL

;with cte(id,FirstName,LastName) as(select t1.id,t1.name FirstName,t2.name LastName from temp_cte1 t1 inner join temp_cte2 t2 on t1.id=t2.id)
update cte set id=12232 where FirstName='Yogesh'

ALTER TABLE temp_cte2 CHECK CONSTRAINT ALL

How can foreign key constraints be temporarily disabled using T-SQL?

Community
  • 1
  • 1
DasMensch
  • 376
  • 3
  • 5