Is a Primary Key an index?

Question

Post reply

Is a Primary Key an index?

Phil Factor

SSC-Insane

Points: 20244
More actions
July 15, 2014 at 9:16 pm

#291155

Comments posted to this topic are about the item Is a Primary Key an index?
Best wishes,
Phil Factor
July 16, 2014 at 2:04 am

This was removed by the editor as SPAM

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

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

Irritating because over-pedantic. Good question nevertheless.

Tom

Nathan SSChasing Mays Points: 631 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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: 259105 More actions · Answer 6

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: 22999 More actions · Answer 7

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

Umer Akram SSC-Insane Points: 22209 More actions · Answer 8

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

Phil Parkin SSC Guru Points: 246959 More actions · Answer 9

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?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 10

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 SSC-Insane Points: 20244 More actions · Answer 11

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

Phil Parkin SSC Guru Points: 246959 More actions · Answer 12

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?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Factor SSC-Insane Points: 20244 More actions · Answer 13

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