SQL Server Mysql database to modify the value of the auto-increment column and the corresponding solution to the problem, sqlservermysql

SQL Server Mysql database to modify the value of the auto-increment column and the corresponding solution to the problem, sqlservermysql

Modify the auto-increment column value on the SQL Server Platform

Since I have previously processed SQL server database migration, I tried to change the auto-increment column value. However, it is strictly not allowed to modify the auto-increment column value through SQL statements, 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； -- intEXEC sys.sp_configure@configname = 'show advanced options' , -- varchar(35)@configvalue = 1； -- intRECONFIGURE WITH OVERRIDE;GOUPDATE sys.syscolumnsSET colstat = 1WHERE id = OBJECT_ID(N'PrimaryKeyAndIdentityUpdateTestDataTable', 'U')AND name = N'ID'AND colstat = 1;UPDATE sys.columnsSET is_identity = 0WHERE object_id = OBJECT_ID(N'PrimaryKeyAndIdentityUpdateTestDataTable', 'U')AND name = N'ID'AND is_identity = 1;

The execution result is as follows:

Modify the auto-incrementing column value on the MySQL Platform

It is troublesome to modify the auto-increment 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-increment values (such as 1 and 2) into the following three steps:
1. First, change the auto-increment column value to 0;
2. Modify the auto-increment column value 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; -- directly modify the uncertain -- update identity_datatable -- set id = case when id = 1 then 2 when id = 2 then 1 end -- where id in (1, 2 ); update identity_datatableset id = 0 where id = 1; update identity_datatableset id = 1 where id = 2; update identity_datatableset id = 2 where id = 0; select * from identity_datatable;

Data Table results before modification, such:

The modified data table result, 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_myisamset id = 0;where id = 1;select *from autoincremenet_datatable_myisam;update autoincremenet_datatable_myisamset id = 1;where id = 2;select *from autoincremenet_datatable_myisam;update autoincremenet_datatable_myisamset 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.

Articles you may be interested in:

• SQL SERVER auto-increment Column
• How SQL Server modifies the ID column, such as batch modification of the auto-incrementing Column
• MySQL auto-increment column insert 0 values Solution
• How to reset the initial value of AUTO_INCREMENT in the auto-increment column of mysql
• How to add an auto-increment column in SQL Server 2008 to implement auto-increment sequence number
• In-depth exploration of the causes of primary key duplication caused by mysql auto-increment Columns