Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Is a Primary Key an index? Expand / Collapse
Author
Message
Posted Wednesday, July 16, 2014 2:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:10 PM
Points: 7,416, Visits: 17,967
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

Never attribute to malice that which is adequately explained by stupidity
Post #1592941
Posted Wednesday, July 16, 2014 2:08 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:36 AM
Points: 15,289, Visits: 13,074
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
Post #1592943
Posted Wednesday, July 16, 2014 2:08 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, August 15, 2016 10:16 AM
Points: 646, Visits: 2,864
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
Post #1592944
Posted Wednesday, July 16, 2014 2:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:10 PM
Points: 7,416, Visits: 17,967
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

Never attribute to malice that which is adequately explained by stupidity
Post #1592946
Posted Wednesday, July 16, 2014 2:15 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, August 15, 2016 10:16 AM
Points: 646, Visits: 2,864

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
Post #1592947
Posted Wednesday, July 16, 2014 2:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:10 PM
Points: 7,416, Visits: 17,967
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

Never attribute to malice that which is adequately explained by stupidity
Post #1592960
Posted Wednesday, July 16, 2014 5:39 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:40 PM
Points: 9,330, Visits: 8,668
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
Post #1593004
Posted Wednesday, July 16, 2014 5:44 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:36 AM
Points: 15,289, Visits: 13,074
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
Post #1593006
Posted Wednesday, July 16, 2014 5:51 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 1,555, Visits: 2,928
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.
Post #1593007
Posted Wednesday, July 16, 2014 6:04 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:36 AM
Points: 15,289, Visits: 13,074
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
Post #1593012
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse