Is a Primary Key an index?

  • Phil Factor

    SSCoach

    Points: 19959

    Comments posted to this topic are about the item Is a Primary Key an index?

    Best wishes,
    Phil Factor
    Simple Talk

  • TomThomson

    SSC Guru

    Points: 104772

    Irritating because over-pedantic. Good question nevertheless.

    Tom

  • nimbell

    SSChasing Mays

    Points: 631

    Question didn't really make an adequate distinction between the logical and the physical:

    Does the logical definition of a Primary Key imply the presence of an index? No

    In SQL Server, does the presence of a Primary Key imply some kind of index is also present? Yes

  • pmadhavapeddi22

    SSCertifiable

    Points: 5306

    This is my understanding. primary key is a constraint not an index, but it creates an index automatically by default, but primary key is called a constraint not an index though. so I got it correct (though I had to think little bit)

    Good question πŸ™‚

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Thank you for the post, simply good one. (for me the question was simple enough)

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    pmadhavapeddi22 (7/15/2014)


    This is my understanding. primary key is a constraint not an index, but it creates an index automatically by default, but primary key is called a constraint not an index though. so I got it correct (though I had to think little bit)

    Good question πŸ™‚

    +1

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    I remembered that it is sometimes called a "primary key constraint", so the question was easy enough.

    Still I expect some flaming in this topic πŸ™‚

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Neil Burton

    SSC-Insane

    Points: 22079

    I got it wrong because I misinterpreted this line:

    When you specify a PRIMARY KEY constraint for a table, the Database Engine enforces data uniqueness by creating a unique index for the primary key columns.

    It creates an index but doesn't use the PRIMARY KEY as that index. Annoying, but my fault.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    β€”Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • twin.devil

    SSC-Insane

    Points: 22208

    Koen Verbeeck (7/16/2014)


    I remembered that it is sometimes called a "primary key constraint", so the question was easy enough.

    Still I expect some flaming in this topic πŸ™‚

    +1, Nice question

  • This was removed by the editor as SPAM

  • Phil Parkin

    SSC Guru

    Points: 244004

    Regarding this

    ... default candidate key to be used to select particular rows of the table

    Can anyone expand on what is meant by 'default' here?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Phil Parkin (7/16/2014)


    Regarding this

    ... default candidate key to be used to select particular rows of the table

    Can anyone expand on what is meant by 'default' here?

    When there are multiple candidate keys, the primary key is the one you turn to first. Hence the name primary key.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Phil Factor

    SSCoach

    Points: 19959

    Irritating because over-pedantic. Good question nevertheless.

    Thanks. The reason I felt motivated to enter it as a QOTD was that I became intrigued by the way that constraints were recorded in the metadata. Unless you appreciate the difference between a key and an index, it can be rather baffling.

    I think I ought to follow up with 'Is a Foreign Key an index? If no, is it enforced by an index?'

    Best wishes,
    Phil Factor
    Simple Talk

  • Phil Parkin

    SSC Guru

    Points: 244004

    Koen Verbeeck (7/16/2014)


    Phil Parkin (7/16/2014)


    Regarding this

    ... default candidate key to be used to select particular rows of the table

    Can anyone expand on what is meant by 'default' here?

    When there are multiple candidate keys, the primary key is the one you turn to first. Hence the name primary key.

    Hmm - "the one you turn to first" - what do you mean, practically?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Phil Factor

    SSCoach

    Points: 19959

    Regarding this

    ... default candidate key to be used to select particular rows of the table

    Can anyone expand on what is meant by 'default' here?

    In addition to what's been said: In SQL, you can define a foreign key by specifying only the table it relates to, leaving out the key. If you do that, the primary key is chosen from the possible candidate keys. There is some debate among relational theorists as to whether a properly normalised table will have more than one candidate key but, heck, we've all seen them, hence the usefulness of having a primary key.

    Best wishes,
    Phil Factor
    Simple Talk

Viewing 15 posts - 1 through 15 (of 55 total)

You must be logged in to reply to this topic. Login to reply