Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ How to modify the SQL Server auto-increment value and the corresponding solution     - Linux cron job (Linux)

- LNMP Note: Addressing mail function can not send mail (Server)

- CentOS7 yum install third-party source EPEL (Linux)

- Oracle 11g RAC installation considerations Miscellany (Database)

- Computer black magic: tail recursion (Programming)

- Linux rights management (Linux)

- Role Object of registerNatives () method (Programming)

- Ubuntu amend resolv.conf restart failure problem (Linux)

- Mistakenly deleted redo log file group being given the lead to start the database ORA-03113 (Database)

- Install the Red Hat Container Development Kit on OSX (Server)

- Linux ./configure --prefix command (Linux)

- Installation Mate Desktop in FreeBSD 10.1 (Linux)

- Binary tree traversal algorithm summary (recursive and non-recursive) (Programming)

- Java threads in the life cycle (Programming)

- Kitematic how seamless and DockerFILE (Server)

- SQL Server memory Misunderstanding (Database)

- Camera-based face recognition OpenCV crawl and storage format (Python) (Linux)

- Oracle Database Delete Delete million or more common method of heap table data (Database)

- Linux System Getting Started Tutorial: How do you know Shell which is currently being used (Linux)

- Linux run queue process scheduling (Programming)

 
         
  How to modify the SQL Server auto-increment value and the corresponding solution
     
  Add Date : 2018-11-21      
         
         
         
  Encountered in the work of a special task today, it is to conduct two additional self-swap values ​​change.

SQL Server platform to modify the auto-increment value
Since treated before migration sql server database, tried to change its auto-increment value, but modified from the value added by the SQL statement is strictly not allowed, direct error (Can not update identity column 'from additional name' ). sql server I tested 2008,2012 and 2014, are not allowed to change the auto-increment value, I believe that the environment SQL Server 2005+ are not allowed to change the field column values.
If you have to modify the SQL Server platform from added value, since it would require additional manual property, and then modify the column values, and then manually add the amendment is successful auto-increment property. If modified from the inclusion in the build environment, I suggest in my spare time (after 0:00, the user platform or website using very little time) to deal with such problems. Large volumes of data and multi-table associated with it through T-SQL to change. The biggest drawback is the method to assist canceled by hand and adding self-energizing properties.
There is also a method, the first data to be modified finishing as T-SQL script insertion, and then delete these data to be modified, by inserting data display to achieve. This approach applies not less to change a single table record, when the method more flexible.
A more simple way, that is, if only several pieces, then let operators who re-publish the information, delete the previous data.
There are online repaired by T-SQL statements cancel the self-energizing properties, in SQL Server 2005+ I did not pass the test environment, 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;

MySQL platform modified from value added
 
mysql platform modified from value added, some trouble. mysql exists in an auto-increment, if its engine is myisam, the column can be independent primary key column, may be a composite primary key column, the column must be mainly associated with the column bonds; if its engine is innodb, the column must be independent primary key column. To directly modify the two auto-increment value swap change, it is definitely impossible.
The method I used is from two additional values ​​(eg 1,2) is divided into the following three steps to achieve:
1, first modify the value added from 1 to 0;
2, and then modify the value added from 2 to 1;
3, and then modify the value added from 0 to 2;
Two data engine test environment are mysql 5.6.
Innodb database engine under the premise of the specific mysql test code 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 infeasible
- 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;
1, using the method of exchanging two numbers.
2, introduced in the middle of the best value <= 0.
3, only to provide a solution, the method can also be used to modify the sql server platform (1, to cancel a small amount of data since the last increase in property changes increase the self-energizing properties, 2, finishing T-SQL script reinsert ---- when can; 3, manually re-add operations staff, but also a small amount of data in the case).
The database engine under the premise myisam, specific mysql test code 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 test is not feasible.
2, asks "the first update and the following select do see the modified value, but the subsequent sql continue, but are being given to restore the status before the unmodified," this is unclear, need to continue to study.
Oracle platform no contact, I do not know, familiar with the oracle platform blog friends to be tested or for which a summary is given from additional changes.
     
         
         
         
  More:      
 
- Linux --- file descriptors and redirection (Linux)
- Reset CentOS / RHEL root account password 7 (Linux)
- Learn to read the source code of vmstat (Linux)
- Linux mount command Detailed (Linux)
- MySQL display operation control tips (Database)
- Spring AOP custom annotation way to achieve log management (Programming)
- How to manage and use Logical Volume Management LVM in Ubuntu (Linux)
- Sublime Text 3 best features, plug-ins and settings (Linux)
- GitLab Installation Guide -Ubuntu 14.04 LTS (Server)
- Piostat - Monitoring and Statistics Linux process (Linux)
- Linux System Getting Started Tutorial: Using the Linux common commands (Linux)
- MultiWriter: while the ISO image concurrent writes 20 USB Startup Disk (Linux)
- Notebook computer forget password solution (Linux)
- Compare Several MySQL environmental issues (Database)
- Linux set the maximum number of open files nofile and nr_open, file-max Description (Linux)
- Share useful bash aliases and functions (Linux)
- Linux how to handle file names that contain spaces and special characters (Linux)
- LaTeX Getting Started Tutorial (Linux)
- Ubuntu users to install voice switch instructs the applet (Linux)
- The array of C language (Programming)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.