Geek Answers Handbook

Database "key / ID" design ideas, surrogate key, primary key, etc.

So, I have seen several references to the surrogate key recently, and I'm not quite sure what it is and how it differs from the primary key.

I always assumed that the identifier was my primary key in a table like this:

Users
    ID, Guid
    FirstName, Text
    LastName, Text
    SSN, Int

however, wikipedia defines a surrogate key as "A surrogate key in a database is a unique identifier for an object in the simulated world or an object in a database. The surrogate key is not obtained from the application data."

According to Wikipedia, does it look like the ID is my surrogate key, and my primary key could be SSN + ID? It is right? Is this a bad desk design?

Assuming the design of the table sounds, would it be something like this bad for a table where the data had nothing unique about it?

LogEntry
    ID, Guid
    LogEntryID, Int [sql identity field +1 every time]
    LogType, Int
    Message, Text
+5
source share
7 answers

No, your identifier can also be a surrogate key (which simply means that it is not "derived from application data", for example, an artificial key), and it should also be your primary key.

The primary key is used to uniquely and securely identify any row in your table. It must be stable, unique and NOT NULL - an “artificial" identifier usually has these properties.

"" - 150% , ?? SSN, , , ( ) - . , ......

, () , , ( ).

, "" - , , .

+12

-, Surrogate - , , .

- . , . , . , .

  • ! , . . . . ( , , , , , )
  • -, () , . ( ) , , ) , SQL. B) , , FK.

, ... ( ) , , ! , (, ) FK . , , .

, . , ... . , BankAccount, . ! , ,

id  BankRoutingNumber BankAccountNumber   BankBalance
 1     12345678932154   9876543210123       $123.12
 2     12345678932154   9876543210123    ($3,291.62)

, ?

: " , " "? , , , ! , FK, , / , , .

, . , , , . , . , .

" " , , , , FK, , .

+6

, , , , , , , - .

, , . , , ? 2, 3 n . email_address ( - ), , .

, id , . , 10 , . , ? ! . , , , ..

, - , , , . , , ( client_id, email, category_id ), ! , , , . !

, , , .

+4

, . ( ). , . .

SQL Server Auto-increment Identity, SQL Server . , . . . , . , , , .

, GUID . GUIDS . , , .

+3

Guid Users .

LogEntry

LogEntry , int, Guid. , , .

+1
  • - , . , , . .
  • . , .

( ) . , , . , . - .

, , .

0

, . - , . - , . - , .

, .. , , ( ). , , , " ". ( ), (, 20091222001), - . , , , VIN , ( ), .

. , , , " ". , .

, LogEntryID . FK ""? , , ID, LogEntryID , . , user_id - .

0

More articles:


All Articles