When we establish a database, we need to specify a primary key for each table. The so-called primary key is the properties or attribute group that can uniquely identify a row in the table, and a table can only have a primary key, but there can be multiple candidate indexes. Because the primary key can uniquely identify a row record, you can ensure that the data update is executed, and when the delete is deleted, Zhang Guan Li's error will occur. Of course, other fields can assist in eliminating sharing conflicts when performing these operations, but will not be discussed here. The primary key is often constituted with foreign bonds in addition to the above functions, and the data is inconsistent with the foreign bond to prevent data inconsistency. So the database is a very important role when designing.
Common database primary keys are:
Automatic growth field Manual growth field uniqueidentifier "Comb (combine) type
First, automatic growth field
Many database designers prefer to use automatic growth fields because it is easy to use. The automatic growth field allows us to use the value of the primary key when adding data to the database, the database system automatically assigns a value to it, ensuring that repetition will never be repeated. If you use the SQL Server database, we can also use the @@ identity global variable to get the primary key key value of the system allocated after the record is inserted.
Although the automatic growth field will save our many cumbersome work, there is a potential problem in the use of it, that is, it is difficult to fill in the primary key and foreign key in data buffer mode. Suppose there are two tables:
Order (OrderId, OrderDate) Orderdetial (OrderID, Linenum, ProductID, Price)
ORDERID in the Order table is an automatic growth field. Now we need to enter a order, including inserting a record in the ORDER table and inserting several records in the OrderDetail table. Because ORDERID in the ORDER table is an automatic growth field, then we cannot know the value before the record is officially inserted into the database, only what the database is assigned to it after the update is updated. This will cause the following contradictions:
First, in order to add the correct value in the OrderDetail's ORDERID field, you must first update the ORDER table to get the ORDERID value assigned by the system, and then fill the ORDERDETAIL table with this ORDERID. Finally update the OderDetail table. However, in order to ensure data consistency, ORDER and ORDERDETAIL must be performed at the same time under transaction protection, that is, ensure that both tables are successful. Obviously they are contradictory.
In addition, when we need to copy data between multiple databases (SQL Server data distribution, the subscription mechanism allows us to make data copy operations between the library), the automatic growth field may cause the primary key conflict when data merge . Imagine the ORDER table in a database to replicate the database in another library, what does the ORDERID do not grow automatically?
ADO.NET allows us to set a field to an automatic growth field in DataSet, but remember that this automatic growth field is just a placeholder. When the database is updated, the database generated automatically replaces ADO The value of .NET assigned. Therefore, in order to prevent misunderstandings, it is recommended that you set the automatic growth initial values and increments in ADO.NET to -1. In addition, in ADO.NET, we can establish DataRelation for two tables. When there is two table updates, a table update, another table corresponding button will automatically change, this will It greatly reduces the trouble of automatic growth fields when we update the two tables with the presence cascade relationship.
Second, manual growth field
Since the automatic growth field will bring such trouble, we may wish to consider using manual growth fields, that is, the value of the primary key requires its own maintenance, usually requires a separate table storage current primary key key value. In the example of the above example, this time we have built a table called INTKEY, contains two fields, keyname, and keyvalue. Just like a HashTable, give a keyName, you can know what the current keyValue is, and then manually implement the key value data increments. You can write such a stored procedure in SQL Server, let the procedure of the key value are automatically performed. The code is as follows: CREATE
Procedure
[
GetKey
]
@KeyName
charr
(
10
), @KeyValue
int
OUTPUT
AS
Update
Intkey
Set
@KeyValue
=
KeyValue
=
KeyValue
1
WHERE
Keyname
=
@KeyName
Go
This way, we can get the latest key value by calling the stored procedure, ensuring no repetitions. If the ORDERID field is set to manual growth field, our program can be implemented by the following steps: first call the stored procedure, get an ORDERID, then use this ORDERID to fill the Order table with the OrderDetail table, and finally two tables under transaction protection Update.
When using manual growth fields as a primary key, when performing database data replication, you can ensure that the key value conflicts will occur during data merge, as long as we assign different primary key values to different databases. However, using the manual growth field increases the network's Roundtrip, we must obtain the current primary key key value by adding a database access, which increases the network and database load, when in a low-speed or disconnected network environment, this There is a lot of drawbacks. At the same time, manual maintenance main keys should also consider various factors such as concurrent conflict, which will increase the complexity of the system.
Third, use uniqueidentifier
SQL Server provides us with a UNIQUEIDentifier data type and provides a generated function newid (), using newid () to generate a unique UNIQueIdentifier. UniqueIdentifier occupies 16 bytes in the database, and the probability of repetition is very small, so that it can be considered 0. We often see similar from the registry
{45f0eb02-0727-4f2e-aab5-e8aedee0cec5}
The thing is actually a uniqueidentifier, Windows uses it to do COM components and identity of the interface to prevent repetition. In .NET in the .NET, Uniqueidentifier, called GUID (Global Unique Identifier). You can use the following command to generate a guid in C #:
Guid U
=
System.guid.newguid ();
For the ORDER and ORDERDETAIL mentioned above, if you use UNIQUEIDENTIFIER as the primary key, we can avoid the problem of increasing the network Roundtrip mentioned above. Generate the GUID fill the primary key through the program, do not consider whether it will repeat.
There is also a serious flaw in the UNIQueIdentifier field: First, its length is 16 bytes, which is 4 times that of the integer, which will occupy a lot of storage space. More seriously, UniqueIdentifier's generation is unregulated. To establish an index above (most databases have index on primary keys) is a very time consuming operation. Some people have done experiments, insert the same amount of data, use UNIQUEIDENTIFIER data to do the primary key than using Integer type data, so avoid using the UNIQUEIDentifier database as the primary key key value for efficiency. Fourth, use "Comb (Comb (Combine) type
Since the three primary key types of primary key have their own shortcomings, if there is any good way to solve it? The answer is yes. By using the COMB type (there is no COMB type in the database, it is designed in his "The Cost of GUIDS AS Primary Keys", you can find a good balance between the three.
The basic design idea of the COMB data type is this: Since the UniqueIdentifier data is low in index efficiency due to irregular, it affects the performance of the system, then we can keep the first 10 bytes of UNIQUEIDENTIFIER, After 6 bytes indicate the time (DateTime) generated, we combine the time information with UniqueIdentifier to reserve uniqueness of UNIQueIDentifier, while increasing orderability, to increase index efficiency. Maybe someone will worry that UNIQUEIDENTIFIER will decrease to 10 bytes, which can cause repetition. Don't worry, the time accuracy of the next 6 bytes can reach 1/300 seconds, the two COMB type data is identical to the same possibility is here 1/300 The first 10 bytes generated in seconds are identical, which is almost impossible! This idea is implemented using the SQL command in SQL Server.
Declare
@AGUID
UniqueIdentifier
Set
@AGUID
=
CAST
(
CAST
(
NewID
()
AS
Binary
(
10
))
CAST
(
Getdate
()
AS
Binary
(
6
))
AS
UniqueIdentifier
)
After testing, use the COMB to do the primary key than using Int, it is still slow to retrieve, insert, update, delete, etc., but it is better than the unidentifier type. About test data can refer to my essay on July 21, 2004.
In addition to using the stored procedure, we can also generate COMB data using C #, so all primary key generation work can be done at the client. The C # code is as follows:
//
============================================================================================================================================================================================================= ==============
/ ** /
///
public
Static
GUID newcomb ()
{Byte [] guidarray = system.guid.newguid (). TobyTearray (); datetime based = new datetime (1900, 1, 1); datetime now = datetime.now; // get the days and milliseconds Which Will Be buy BUILD The BYTE STRING TIMESPAN Days = New TimeSpan; Timespan Msecs = New TimeSpan (now.tyear, now.month, now.day)); // Convert to a byte array // Note that SQL Server is accurate to 1 / 300th of a millisecond so we divide by 3.333333 byte [] daysArray = BitConverter.GetBytes (days.Days); byte [] msecsArray = BitConverter.GetBytes ( (long) (msecs.TotalMilliseconds / 3.333333)); // Reverse the bytes to match SQL Servers ordering Array.Reverse (daysArray); Array.Reverse (msecsArray); // Copy the bytes into the guid Array.Copy (daysArray, DaysArray.Length - 2, Guidarray, Guidarray.Length - 6, 2); Array.copy (MsecsArray, MsecsArray.Length - 4, Guidarray, Guidarray.length - 4, 4); Return new system.guid (guidarray);
//
============================================================================================================================================================================================================= ==============
/ ** /
///
Static
DateTime getdatefromcomb (system.guid guid)
{Datetime based = new datetime (1900, 1, 1); byte [] daysarray = new byte [4]; byte [] msecsArray = new byte [4]; byte [] guidarray = guid.tobyteaRray (); // Copy THE date Parts of the guid to the respective byte arrays. Array.copy (Guidarray, Guidarray.Length - 6, Daysarray, 2, 2); Array.copy (Guidarray, Guidarray.Length - 4, MsecsArray, 0, 4); // Reverse the arrays to put them into the appropriate order Array.Reverse (daysArray); Array.Reverse (msecsArray); // Convert the bytes to ints int days = BitConverter.ToInt32 (daysArray, 0); int msecs = BitConverter. Toint32 (MsecsArray, 0); DateTime Date = Basedate.Adddays (days); Date = DATE.AddmilliseConds (msecs * 3.333333); return date;}
Conclusion
The database main key has an important position in the database. The selection policy of the primary key determines if the system is efficient, easy to use. This paper compares the advantages and disadvantages of four primary keys, and provides the corresponding code solution, I hope to help everyone.
