How do I modify the value of the SQL primary key column?

linfenfang

For example, there is a table
create table Province
(
   ProID int primary key,
   ProName varchar (20) not null
)
The inserted data is: (1, Guangzhou)
So how to modify the primary key value with SQL statement so that it becomes (2, Guangzhou)
Waiting online ...

fanfan1980

Is there a foreign key that references the primary key?

fanfan1980

update Province set ProID = 2
where ProID = 1

fanfan1980

create table Province
(
   ProID int primary key,
   ProName varchar (20) not null
)
insert Province select 1, 'Guangzhou'

update Province set ProID = 2
where ProID = 1

select * from Province

--result
ProID ProName
----------- --------------------
2 Guangzhou

(1 row (s) affected)

四不像君

Make sure that the primary key does not conflict, and there is no foreign key that references the primary key.
update Province set ProID = 2
where ProID = 1 and ProName like 'Guangzhou'

linfenfang

Thank you upstairs, suddenly I was wrong ...
What if there are foreign key references?

zzclark

Set the foreign key relationship to on update cascade, directly update the primary key of the primary table, and the foreign key of the secondary table will be cascaded automatically.

linfenfang

Thank you, I've now understood..

bobopu

No foreign key, feel free
In the case of foreign keys, cascading is required