1, the primary key defined as the automatic growth identifier type
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:
Copy Code code as follows:
CREATE TABLE Customers (ID int auto_increment PRIMARY key NOT NULL, name varchar (15));
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:
Copy Code code as follows:
CREATE TABLE Customers (ID int identity (1,1) primary key NOT NULL, name varchar (15));
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:2013-6-4
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:
Copy Code code as follows:
Use TESTDB2
--STEP1: Creating a Table
CREATE TABLE Customers (
ID int identity PRIMARY key NOT NULL,
Name varchar (15)
);
--STEP2: Perform insert operation
INSERT into Customers (Id,name) VALUES (1, ' name1 ');
--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 the self-growth of the primary key column
SET Identity_insert customers on;
--STEP4: Insert two records, the primary keys are 1 and 3 respectively. Insert Succeeded
INSERT 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 Succeeded
INSERT 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.
Copy Code code as follows:
Create sequence Customer_id_seq increment by 2-start with 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 a 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.
Copy Code code as follows:
CREATE TABLE Customers (ID int primary key NOT NULL, name varchar (15));
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
Copy Code code as follows:
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
Copy Code code as follows:
INSERT into customers (name) VALUES (' Test ');
This is where we will find that this record is inserted into the database, and the ID is growing from it.
