Methods for defining self-growing columns in common databases

MYSQL

In MySQL, if the primary key of the table is set to the Auto_increment type, the database is automatically assigned the primary key. For example:

CREATE TABLE Customers (ID int auto_increment PRIMARY key NOT NULL, name varchar);
INSERT into customers (name) VALUES ("Name1"), ("name2");
Select ID from Customers;

The above SQL statement creates the Customers table, inserts two records, and only sets the value of the Name field at the time of insertion. The last Query table ID field, the result of the query is:

This shows that once the ID set to the auto_increment type, the MySQL database will automatically be incremented in the form of the primary key assignment. SQL Server

In MS SQL Server, if the primary key of the table is set to the identity type, the database is automatically assigned the primary key. For example:

CREATE TABLE Customers (ID int identity (1,1) primary key NOT NULL, name varchar);
INSERT into customers (name) VALUES (' name1 '), (' name2 ');
Select ID from Customers;

Note: strings are expanded in single quotes in SQL Server, and double quotes can be used in MySQL.

The query results are the same as MySQL.

This shows that once the ID is set to the identity type, the MS SQL Server database is automatically assigned the primary key in an incremental manner. The identity contains two parameters, the first parameter represents the starting value, and the second parameter represents an increment. ps:2012-7-13

It's been a lot of times before when we deleted a record from the growth list of 1, and the record we inserted again was 2. We want to insert a record from the growth list of 1 is not to be done. When I talk to my colleagues today, I find that we can cancel the growth by setting set Identity_insert <table_name> on, and then turn off the feature after we insert the data. The experiment is as follows:

use TESTDB2--step1: Create a table creation table customers (ID int identity PRIMARY key not NULL, name Var

char ( ));
--STEP2: Inserts into Customers (Id,name) VALUES ( 1 , ' name1 ') performed insert operation; --Error: An explicit value for the Identity column in table ' customers ' can are specified when a column list is used and I

Dentity_insert is on.

--STEP3: Release from the growth set IDENTITY_INSERT customers on the primary key column; --STEP4: Insert two records, the primary keys are 1 and 3 respectively.
Insert successfully inserted into customers (Id,name) VALUES ( 1 , ' name1 ');

INSERT into Customers (Id,name) VALUES ( 3 , ' name1 '); --STEP5: Inserts a record with a primary key of 2 again.

Insert successfully inserted into customers (Id,name) VALUES ( 2 , ' name1 '); --STEP6: Insert duplicate PRIMARY key,--error: Violation of PRIMARY key constraint ' pk__customer__3213e83f00551192 '.
Cannot insert duplicate key in object ' Dbo.customers '.

INSERT into Customers (Id,name) VALUES ( 3 , ' name1 '); --STEP7: Close Identity_insert SET identity_insert customers off; 

2. Get the automatic growth identifier from the sequence Oracle

In Oracle, you can create a separate sequence for the primary key of each table, and then get the automatically incremented identifier from the sequence, assigning it to the primary key. For example, the statement creates a sequence named Customer_id_seq, which starts with a value of 1 and an increment of 2.

2 1

Once you have defined the CUSTOMER_ID_SEQ sequence, you can access the Curval and Nextval properties of the sequence. Curval: Returns the current value of the sequence Nextval: first increment the value of the sequence, then return the sequence value

The following SQL statement creates the Customers table, inserts two records, sets the ID and the value of the Name field at the time of insertion, where the value of the ID field comes from the customer_id_seq sequence. The last query customers the ID field in the table.

CREATE TABLE Customers (ID int primary key NOT NULL, name varchar);
INSERT into customers values (Customer_id_seq.nextval, ' name1 ');
INSERT into customers values (Customer_id_seq.nextval, ' name2 ');
Select ID from Customers;

If you execute the above statement in Oracle, the query results are:

automatically add an ID field through a trigger

As you can see from the INSERT statement above, it can be cumbersome and cumbersome to insert the value of the Customer_id_seq.nextval each time, so consider using triggers to do this step.

Creating triggers Trg_customers

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

This is where we will find that this record is inserted into the database, and the ID is growing from it.