SQL Primary key and surrogate key

Tags:
SQL
When surrogate key is generated automatically, then why is a primary key required?


Software/Hardware used:
Sql
1

Answer Wiki

Thanks. We'll let you know when a new response is added.
If I understand the question correctly you are asking why a primary key *constraint* is needed when the key values are generated automatically. In practice most native DBMS key-generator features can be bypassed or overridden, or the generated value may get updated after it is generated. Under some unusual circumstances a key generator may get reset so that it produces duplicate values. For these reasons the key-generator by itself won’t necessarily guarantee uniqueness.

Adding a primary key constraint not only guarantees uniqueness, it also provides information to the query optimizer (if one exists) to help improve query performance and the presence of a constraint shows database users what the key attribute is. Creating a constraint usually means an index is also created, and normally it’s desirable to have an index for key columns. Data management software tools tend to rely on constraints to identify keys – such tools may not be able to use a generated attribute as a key unless it has a constraint on it.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    Do you understand what a surrogate key is?

    A surrogate key is most of the times used as primary key. They are not mutually exclusive things.
    84,745 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: