Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is a Primary Key an index?


Is a Primary Key an index?

Author
Message
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8373 Visits: 19499
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?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

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.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16459 Visits: 13207
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.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Phil Factor
Phil Factor
Right there with Babe
Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)

Group: General Forum Members
Points: 747 Visits: 2937
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
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8373 Visits: 19499
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?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

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.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Phil Factor
Phil Factor
Right there with Babe
Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)

Group: General Forum Members
Points: 747 Visits: 2937

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
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8373 Visits: 19499
Phil Factor (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?

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.


I didn't know this, thank you - 'default' makes sense based on this.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

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.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10303 Visits: 9586
Knowing that the constraint is indeed different than the index, I thought it was a simple enough question, but I admit to stopping and thinking "hmmm...I wonder how to interpret this" because the clustered index is created when you create the primary key constraint. An interesting question.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16459 Visits: 13207
Ed Wagner (7/16/2014)
"hmmm...I wonder how to interpret this"


Well, a primary key (constraint) can be considered an object at the logical level, while an index exists purely at the physical level.
So it is 100% possible to decouple the primary key from the index, as the latter one is just a matter of implementation.

Ed Wagner (7/16/2014)
... because the clustered index is created when you create the primary key constraint..


Unless a clustered index was already defined. :-)



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Raghavendra Mudugal
Raghavendra Mudugal
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1682 Visits: 2958
Koen Verbeeck (7/16/2014)
[quote]
Unless a clustered index was already defined. :-)


how you mean? "already"

this simple statement creates a clustered index
 
CREATE TABLE TT1
(
ID INT PRIMARY KEY,
NAME VARCHAR(10)
);



ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16459 Visits: 13207
Raghavendra Mudugal (7/16/2014)
Koen Verbeeck (7/16/2014)
[quote]
Unless a clustered index was already defined. :-)


how you mean? "already"

this simple statement creates a clustered index
 
CREATE TABLE TT1
(
ID INT PRIMARY KEY,
NAME VARCHAR(10)
);



You can create a table as a heap, create a clustered non-unique index on some columns and then define a primary key.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search