Is a Primary Key an index?

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

    Best wishes,
    Phil Factor

  • Irritating because over-pedantic. Good question nevertheless.

    Tom

  • 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

  • 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 πŸ™‚

  • 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.

  • 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.

  • 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

  • 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

  • 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

  • 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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

  • 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

  • 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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

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

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