How does SQL Server modify the auto-increment column value and corresponding solutions?

How does SQL Server modify the auto-increment column value and corresponding solutions?

A special task encountered in today's work is to change the values of the two auto-incrementing columns.

Since the SQL Server platform has previously migrated the SQL server database, it has tried to change its auto-increment value. However, it uses SQL statements to modify the auto-increment value, is strictly not allowed. An error is reported directly (the 'auto-incrementing column name' cannot be updated '). I have tested SQL server 2008, 2012, and 2014. I believe that the values of auto-increment Columns cannot be changed in SQL Server 2005 + environments. If you want to modify the auto-increment column value on the SQL Server platform, you need to manually add the auto-increment column attribute, modify the column value, and then manually add the auto-increment column attribute after modification. If you modify the auto-increment column in the Generation Environment, we recommend that you handle this type of problem during idle time (after, there are very few users on the platform or website. Large data volume and multi-Table Association, then through the T-SQL to change. The biggest disadvantage of this method is to manually cancel and add auto-increment attributes. There is also a way to first sort the data to be modified into the insert script of the T-SQL, and then delete this batch of data to be modified in the display to insert the data to achieve. This method is applicable to the single table records with few changes. This method is flexible at that time. The simpler method is to have operators re-publish information and delete previous data if there are only a few items. Also online through repair T-SQL statement to cancel the auto-increment attribute, I have not passed the SQL Server 2005 + environment test, the corresponding T-SQL code is as follows:

 
EXEC sys.sp_configure
    @configname = 'allow updates', -- varchar(35)
    @configvalue = 1； -- int
 
EXEC sys.sp_configure
    @configname = 'show advanced options'  , -- varchar(35)
    @configvalue = 1； -- int
 
RECONFIGURE WITH OVERRIDE;
GO
 
UPDATE sys.syscolumns
SET colstat = 1
WHERE id = OBJECT_ID(N'PrimaryKeyAndIdentityUpdateTestDataTable', 'U')
    AND name = N'ID'
    AND colstat = 1;
 
UPDATE sys.columns
SET is_identity = 0
WHERE object_id = OBJECT_ID(N'PrimaryKeyAndIdentityUpdateTestDataTable', 'U')
    AND name = N'ID'
    AND is_identity = 1; 

The execution result is as follows: it is troublesome to modify the auto-incrementing column value on the MySQL platform. Mysql has an auto-increment column. If its engine is myisam, this column can be an independent primary key column or a composite primary key column, that is, this column must be an associated column of the primary key; if the engine is innodb, the column must be an independent primary key column. To directly modify the values of the two auto-incrementing columns, it is definitely not feasible. The method I used is to divide two auto-incrementing column values (such as 1 and 2) into the following three steps: 1. First, change the auto-incrementing column value to 0; 2. Change the value of the auto-increment column to 2 to 1; 3. Change the value of the auto-increment column to 0 to 2; the test environment for the following two data engines is mysql 5.6. When the database engine is innodb, the specific mysql test code is as follows:

drop table if exists identity_datatable;
create table identity_datatable (
id int not null AUTO_INCREMENT,
name varchar(10) not null,
primary key (id)
) engine=innodb,default charset=utf8;
insert into identity_datatable (id, name)
values (1, '1'),(2,'2');
insert into identity_datatable (id, name)
values (3, '3'),(4,'4');
Select *
from identity_datatable;
--Direct modification is not feasible
-- update identity_datatable
-- set id = case when id = 1 then 2 when id = 2 then 1 end
-- where id in (1, 2);
update identity_datatable
Set id = 0
where id = 1;
update identity_datatable
Set id = 1
where id = 2;
update identity_datatable
Set id = 2
where id = 0;
Select *
from identity_datatable;

The results of the data table before modification, such as the results of the modified data table, for example, Note: 1. Two numbers are used for exchange. 2. The introduced median value is better than or equal to 0. 3. Only one solution is provided. You can also use the modification method of the SQL server Platform (1. Cancel the auto-increment attribute first, and then add the auto-increment attribute after the change, 2, sorting T-SQL script re insert ---- small data volume can; 3, operators manually re add, is also a small amount of data ). The mysql test code is as follows:

drop table if exists autoincremenet_datatable_myisam;
create table autoincremenet_datatable_myisam (
  tid int not null,
  id int not null auto_increment,
  name varchar(20) not null,
  primary key(id)
) engine = myisam, default charset = utf8;
 
insert into autoincremenet_datatable_myisam (tid, id, name)
values(1,1,'a'),(2,2,'b'),(3,3,'c'),(4,4,'d');
 
select *
from autoincremenet_datatable_myisam;
 
update autoincremenet_datatable_myisam
set id = 0;
where id = 1;
 
select *
from autoincremenet_datatable_myisam;
 
update autoincremenet_datatable_myisam
set id = 1;
where id = 2;
 
select *
from autoincremenet_datatable_myisam;
 
update autoincremenet_datatable_myisam
set id = 2;
where id = 0;
 
select *
from autoincremenet_datatable_myisam; 

Note: 1. Changes in the above tests are not feasible. 2. Question: "The first update and the select statement after it do see the modified value, but the subsequent SQL statements continue to run, both report an error and restore the status before the modification", this is not clear yet and further research is required. Oracle platform is not in contact with, do not know, familiar with oracle platform Bo friends for their auto increment column changes to a test or give a summary. If you have a better explanation of your questions, please kindly advise. Thank you.

This article permanently updates the link address: