Go deep into Mysql, SqlServer, and Oracle primary key auto-increment settings _ MySQL

Go deep into the setting of automatic growth of primary keys in Mysql, SqlServer, and Oracle bitsCN.com 1. define the primary key as the automatic growth Identifier type
MySql
In mysql, if the table's primary key is set to auto_increment type, the database automatically assigns a value to the primary key. For example:

Create table MERS (id int auto_increment primary key not null, name varchar (15 ));
Insert into MERS (name) values ("name1"), ("name2 ");
Select id from MERS;

The preceding SQL statement first creates the MERS table, then inserts two records, and only sets the value of the name field during the insertion. Finally, query the id field in the table. the query result is:

It can be seen that once the id is setAuto_incrementType. the mysql database automatically assigns values to the primary key in ascending mode.
SQL Server
In MS SQLServer, if the table's primary key is set to the identity type, the database automatically assigns a value to the primary key. For example:

Create table MERS (id int identity (1, 1) primary key not null, name varchar (15 ));
Insert into MERS (name) values ('name1'), ('name2 ');
Select id from MERS;

Note: strings in sqlserver are expanded with single quotation marks, while double quotation marks can be used in mysql.
The query result is the same as that of mysql.

It can be seen that once the id is set to the identity type, the MS SQLServer database automatically assigns a value to the primary key in ascending mode. Identity contains two parameters. The first parameter indicates the start value and the second parameter indicates the increment.
PS: 2013-6-4
In the past, we often encountered such a problem. when we delete a record with the auto-growth column 1, the record with the re-inserted auto-growth column is 2. We cannot insert a record with the auto-growth column 1. During discussions with colleagues today, we found that we can SET IDENTITY_INSERT ON; to cancel auto-growth, and disable this function after data is inserted. The experiment is as follows:

Use TESTDB2
-- Step 1: Create a table
Create table customers (
Id int identity primary key not null,
Name varchar (15)
);
-- Step 2: execute the insert operation.
Insert into MERS (id, name) values (1, 'name1 ');
-- Error: An explicit value for the identity column in table 'customer' can only be specified when a column list is used and IDENTITY_INSERT is ON.
-- Step 3: Release auto-increment of primary key columns
SET IDENTITY_INSERT customers ON;
-- Step 4: insert two records with the primary keys 1 and 3. Inserted successfully
Insert into MERS (id, name) values (1, 'name1 ');
Insert into MERS (id, name) values (3, 'name1 ');
-- Step 5: insert a record with a primary key of 2 again. Inserted successfully
Insert into MERS (id, name) values (2, 'name1 ');
-- Step 6: insert duplicate primary keys,
-- Error: Violation of primary key constraint 'PK _ customer _ 3213E83F00551192 '. Cannot insert duplicate key in object 'dbo. customer '.
Insert into MERS (id, name) values (3, 'name1 ');
-- Step 7: disable IDENTITY_INSERT
SET IDENTITY_INSERT customers OFF;

2. get the auto-increment identifier from the sequence
Oracle
In Oracle, you can create a separate sequence for the primary key of each table, obtain the automatically added identifier from the sequence, and assign it to the primary key. For example, the following statement creates a sequence named mermer_id_seq. the starting value of this sequence is 1 and the increment is 2.

Create sequence customer_id_seq increment by 2 start with 1

Once the customer_id_seq sequence is defined, the curval and nextval attributes of the sequence can be accessed.
• Curval: returns the current value of the sequence.
• Nextval: first increase the sequence value and then return the sequence value.
The following SQL statement first creates the MERS table, then inserts two records, and sets the values of the id and name fields during insertion. The value of the id field comes from the customer_id_seq sequence. Finally, query the id field in the MERS table.

Create table MERS (id int primary key not null, name varchar (15 ));
Insert into MERS values (customer_id_seq.nextval, 'name1 ');
Insert into MERS values (customer_id_seq.nextval, 'name2 ');
Select id from MERS;

If the preceding statement is executed in oracle, the query result is:

Automatically Add id fields through triggers
From the preceding insert statement, we can find that it is very cumbersome and troublesome to insert the customer_id_seq.nextval value every time. Therefore, you can use a trigger to complete this step.
Create trigger trg_mers MERS

Create or replace
Trigger trg_customers before insert on customers for each row
Begin
Select CUSTOMER_ID_SEQ.nextval into: new. id from dual;
End;

Insert a record

Insert into MERS (name) values ('test ');

We will find that this record is inserted into the database, and the id is still growing.

BitsCN.com