how to update primary key value in sql?

Question

I have a country table which have a column Countryid I accidently deleted the CountryId Which Had value=1 and now in state table there is a value of column countryId=1 which is used to fetch the records (states) according to country id. I had inserted the country again but it has different id so how can I update the value again in country table of CountryId which primary key from 2 to

Simply UPDATE the state table's Countryid column to 2 where it is 1. — jarlh, Jan 04 '17 at 10:24
but I have thousands of in others table too in state table which have the countryid=1 — Gaurav_0093, Jan 04 '17 at 10:25
Possible duplicate of [How to update primary key](http://stackoverflow.com/questions/2499246/how-to-update-primary-key) — Bhavanaditya, Jan 04 '17 at 10:30

score 5 · Accepted Answer · answered Jan 04 '17 at 12:21

The direct answer to your question is to use set identity_insert off. The best place to start is with the documentation.

More important, there is a very simple way to avoid these problems in the future: use explicitly declared foreign key relationships. If you had a foreign key constraint:

alter table state
    add constraint fk_state_country foreign key (countryId) references country(countryId);

Then the delete would not have been allowed.

how to update primary key value in sql?

1 Answers1

Linked