Primary keys are generated in three main ways:
A. Automatic database generation
Two. GUID
Three. Development and Creation
Strictly speaking, these three kinds of production methods have a certain intersection point, its positioning mode will be explained below.
The first way is to position it mainly in the self growing Identification seed: You can set the starting value and the growth step. The advantage is that when used, the concurrency task is completely delivered to the database engine management, and you don't have to worry about having two identical IDs when multiple users are in use. The downside is that most databases do not provide direct access to identity IDs, and the way developers generate IDs is transparent and developers can hardly intervene. The migration of data is also not very convenient.
Because of the pros and cons of the above, this self growing ID is typically used to design the underlying table (the underlying information for the system to run, such as the employee table) primary key, and rarely (at all) for master and foreign keys, because when the master-slave table data is generated and associated, the primary table ID must be determined before the association ID from the table can be located.
Example (MsSQL):
Copy Code code as follows:
--Create a test table
CREATE TABLE [Identity] (
Id INT IDENTITY (1,2) not NULL PRIMARY KEY,--Seed starting value 1, step 2
Number VARCHAR UNIQUE not NULL,
Name VARCHAR is not NULL,
Password VARCHAR (123),
Description VARCHAR () NULL
)
--Inserting records
INSERT into [Identity] (number,name,description) VALUES (' 001 ', ' 1st ', ' id=1, because the starting value is 1 ')
INSERT into [Identity] (number,name,description) VALUES (' 002 ', ' 2nd ', ' id=3, because the starting value is 1, Step 2 ')
insert INTO [Identity] (number,name,description) VALUES (' 003 ', ' 3rd ', ' id=5, because the length of the character is too long, error insertion failed, causing this Id to be discarded ')
insert INTO [Identity] (number,name,description) VALUES (' 004 ', ' 4th ', ' id=7 not 5, because the third record insert failed ')
--Retrieving records, viewing results
SELECT * FROM [Identity]
Results:
(1 rows affected)
(1 rows affected)
Message 8152, Level 16, State 14, line 3rd
string or binary data will be truncated.
The statement was terminated.
(1 rows affected)
(3 rows affected)
Id number Name Password Description
1 001 1st 123 Id=1, since the starting value is 1
3 002 2nd 123 id=3, since the starting value is 1, step 2
7 004 4th 123 id=7 not 5 because the third record insertion failed
The second way, the GUID is the globally unique Identifier, also known as the UUID (universally unique Identifier), the globally unique identifier, which typically consists of 32-bit hexadecimal values that contain the network card address, Time and other information. Any two computers will not produce the same GUID, his advantages in uniqueness, when the need for database integration, can save a lot of labor. For example, the head office and branch offices operate independently, all branch data regularly need to be submitted to the headquarters, you can avoid merging data when the primary key conflict problem, while the GUID also has the characteristics of the growth of the logo, without developers too much attention. But the GUID information is big, occupies the space to be also big, the correlation retrieval, the estimate efficiency is also not very high, for 32 bit hexadecimal its readability is also poor, although the primary key has to the user's meaningless, but in the design or the debugging Exchange is very inconvenient.
In the long run, in order to ensure data portability, it is generally preferred to use GUIDs as primary keys.
Example (MsSQL):
Copy Code code as follows:
--Create a test table
CREATE TABLE GUID (
Id uniqueidentifier not NULL PRIMARY KEY,--Of course you can also use a string to save
Number VARCHAR UNIQUE not NULL,
Name VARCHAR is not NULL,
Password VARCHAR (+) DEFAULT (123)
)
--Inserting records
INSERT into GUID (id,number,name) VALUES (NewID (), ' 001 ', ' 1st ')
INSERT into GUID (id,number,name) VALUES (NewID (), ' 002 ', ' 2nd ')
INSERT into GUID (id,number,name) VALUES (NewID (), ' 003 ', ' 3rd ')
--Retrieving records, viewing results
SELECT * from GUID
Results:
Id number Name Password
8E194F55-B4D3-4C85-8667-33BC6CD33BBC 001 1st 123
7141F202-7D0E-4992-9164-5043EC9FC6F6 002 2nd 123
e0e365a0-8748-4656-af24-5d0b216d2095 003 3rd 123
The third way to develop and create, its convenience is controllable, this controllable refers to its composition form, can be plastic, can also be character, you can according to the actual situation to give a variety of composition and production form, said here may be some friends think of automatically generate a number, such as: 20120716001 or pi-201207-0001 and so on, yes, self-creation also applies to these similar applications.
When it comes to self creation, most of the first thoughts are to get Max (Id) +1, which is easy, but in fact it may be necessary to customize (there is a need for a certain amount of information in the production of a number, the primary key is unnecessary) and concurrent processing is not very good. For example, the maximum number in the current table is 1000, and when the C1 and C2 users take this ID processing, the result is 1001, which causes the save to fail. The general practice is to lock the value when it is taken, but performance is a big problem when multiple users are operating frequently, one of the main reasons is the direct operation of the business data table.
In this case, the solution is to use a key-value table to hold the table name, current or next ID and other information, if multiple table IDs in the system use this method, there will be more than one rule record in the key value table, and of course, the IDs of all tables in the entire database can be generated from one source by the same rules. Then a single rule record is required in the key value table.
Here's a look at the evolution (MsSQL) using the example of a key-value table:
Copy Code code as follows:
--Create a key value table
CREATE TABLE KeyTable (
ID INT IDENTITY (1,1) PRIMARY KEY not NULL,
Tcode VARCHAR UNIQUE not NULL,
Tname VARCHAR (m) not NULL,
TKey INT not NULL,
)
Go
--Inserting Test records
INSERT into KeyTable (Tcode,tname,tkey)
VALUES (' T001 ', ' Test ', 0)
Go
--Create a stored procedure that gets the specified table ID, or modify it to a function
CREATE PROCEDURE Up_newtableid
@TCode VARCHAR, @NextID INT OUTPUT
As
DECLARE @CurTKey int, @NextTKey int
BEGIN TRAN Transid
SELECT @CurTKey =tkey
From KeyTable
WHERE Tcode = @TCode
IF @ @ROWCOUNT = 0
BEGIN
ROLLBACK TRAN Transid
RAISERROR (' Warning:no such row is exists ', 16, 1)
Return
End
SET @NextTKey = @CurTKey + 1
--waitfor DELAY ' 00:00:05 '
UPDATE KeyTable
SET TKey = @NextTKey
WHERE Tcode = @TCode
IF @ @ROWCOUNT = 0
BEGIN
ROLLBACK TRAN Transid
RAISERROR (' Warning:no such row is updated ', 16, 1)
Return
End
COMMIT TRAN Transid
SET @NextID = @NextTKey
Go
Execute stored procedure Up_newtableid:
Copy Code code as follows:
DECLARE @NextID INT
EXEC Up_newtableid ' T001 ', @NextID OUTPUT
PRINT @NextID
When you run it, you will find that it is normal and the results are correct. However, if there is a high concurrency situation, multiple users may get the same ID, and if the ID obtained is used to save the records in the corresponding table, then only one user can save the success.
Below simulates the concurrency case, removes the comment waitfor DELAY ' 00:00:05 ' in the above stored procedure Up_newtableid, opens 3 Query Analyzer's form, executes the above statement sequentially.
Expect to get 1,2,3 separately, but you may find that multiple forms run the same result: 1. This means that before the UPDATE statement is executed, everyone gets an ID of 0, so the next value is 1. (The actual value, depending on the parameter size of the delay and the running time by interval)
From this analysis, some friends may think that the UPDATE statement can be executed when the ID is not the original ID? Modify Process:
Copy Code code as follows:
ALTER PROCEDURE Up_newtableid
@TCode VARCHAR, @NextID INT OUTPUT
As
DECLARE @CurTKey int, @NextTKey int
BEGIN TRAN Transid
SELECT @CurTKey =tkey
From KeyTable
WHERE tcode= @TCode
IF @ @ROWCOUNT =0begin
ROLLBACK TRAN Transid
RAISERROR (' Warning:no such row is exists ', 16, 1)
Return
End
SET @NextTKey = @CurTKey +1
WAITFOR DELAY ' 00:00:05 '
UPDATE KeyTable
SET tkey= @NextTKey
WHERE tcode= @TCode and tkey= @CurTKey--plus TKey checksum here
IF @ @ROWCOUNT =0begin
ROLLBACK TRAN Transid
RAISERROR (' Warning:no such row is updated ', 16, 1)
Return
End
COMMIT TRAN Transid
SET @NextID = @NextTKey
Go
If you open a 3 execution process to simulate concurrency, then 2 forms appear:
Message 50000, Level 16, State 1, Process Up_newtableid, line 28th
Warning:no such row is updated
This will see whether a user operation fails due to concurrency, but is earlier than the previous point at which the error appears.
Then there is no better way, from the query to the end of the whole transaction of the update, there will be no other transactions into which to disrupt the way, the answer is clear, there, the use of locks! You need to select the appropriate lock, otherwise the effect will be the same as above.
Copy Code code as follows:
ALTER PROCEDURE Up_newtableid
@TCode VARCHAR, @NextID INT OUTPUT
As
DECLARE @CurTKey int, @NextTKey int
BEGIN TRAN Transid
SELECT @CurTKey =tkey
From KeyTable with (UPDLOCK)--Using an update lock and keeping it to the completion of the transaction
WHERE tcode= @TCode
IF @ @ROWCOUNT =0begin
ROLLBACK TRAN Transid
RAISERROR (' Warning:no such row is exists ', 16, 1)
Return
End
SET @NextTKey = @CurTKey +1
WAITFOR DELAY ' 00:00:05 '
UPDATE KeyTable
SET tkey= @NextTKey
WHERE tcode= @TCode--there is no need to verify that TKey is the same as select
COMMIT TRAN Transid
SET @NextID = @NextTKey
Go
You can open the N (n>=2) Form for testing, and you'll see that all the operations are serialized, and the result is what we want. So annotate or remove the statements that mimic concurrency waitfor DELAY ' 00:00:05 '.
As mentioned earlier, this is also suitable for the document number similar to the form of encoding, as long as the previous code and key value table slightly modified, interested friends can try. If you get this number from the front end and apply it to each record, there may be a possibility of a jump number. If you want to ensure that no jump numbers exist, one solution is to use the jump-number table to periodically scan and apply the jump-number record to other records. Another solution is to place the saved operation of the record in the process of numbering, resulting in a serialized transaction.
As the saying goes, radish and cabbage each other, you use which has your own truth.
