SQL anti-pattern learning Note 22 Pseudo key neat, tidy up the data

2014-10-17 16:31:50

Goal : Organize the data so that discontinuous primary key ID data records become contiguous.

anti-pattern : Fills the suspended data vacancy.

1. Do not assign numbers in sequence

When inserting a new row, the first unassigned primary key number that is found is assigned to the new line by traversing the table, instead of the original auto-assigned pseudo-key mechanism.

With the query statement of select Max (Id) + 1, there is a problem with concurrent access.

2. Renumber existing data rows: it is common practice to find the largest row of the primary key, and then update it with the smallest unused value.

Cons: (1) SQL statement is more troublesome;

(2) You must also update all the child records that reference the row that you reassigned the primary key to;

(3) There is no way to avoid creating new suspended.

3. Differences in manufacturing data

If the other external system relies on the primary key in the database to define the data, then your update operation will invalidate the reference in that system.

Reusing a primary key is not a note because suspended is often caused by some reasonable removal or rollback of the data.

Don't redistribute them because those pseudo-keys seem useless.

how to recognize anti-patterns : May be anti-pattern when the following conditions occur

1. After I roll back an insert operation, how do I reuse the auto-generated identity of the capsule?

Pseudo-keys are not rolled back once they are generated. If you do not want to roll back, the RDBMS must generate a pseudo key within the declaration period of a slap transaction.

This can lead to competition or deadlock when multiple clients insert data concurrently.

2, BugID is 3 of this record what happened?

3. How to find the first unused ID?

4. What if the digital ID of the self-growth shaper has reached the maximum value?

Rational use of anti-patterns :

There is no reason to change the value of a pseudo-key, because its value itself has no significant meaning. If the primary key column has a practical meaning, then this is a natural key, not a pseudo key.

Solution :

The value of the primary key must be unique and not NULL, so you can use the primary key to uniquely determine a row of records, but this is the only constraint for the primary key.

They do not need to be consecutive values to mark a row.

1, define the line number: Use Row_number () or limit and other keywords to achieve;

2. Use GUID: Database globally unique identifier.

Pros: (1) You can generate pseudo-keys concurrently on multiple database servers without worrying about generating the same values.

(2) There is no problem of suspended.

Disadvantage: (1) The value of the GUID is too long, inconvenient input;

(2) The value of the GUID is random, so no rules can be found or the maximum value is relied upon to determine which row is the most recent insert;

(3) The storage of the GUID requires 16 bytes, which consumes more control than the traditional 4 self-test pseudo-key, and the query is slower

conclusion : Pseudo-keys are identified as uniqueness of rows, but they are not line numbers

SQL anti-pattern learning Note 22 Pseudo key neat, tidy up the data