Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: Today @ 5:38 AM
Points: 5,079, Visits: 11,863
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.
Post #1592941
Posted Wednesday, July 16, 2014 2:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 12,963, Visits: 10,735
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1592943
Posted Wednesday, July 16, 2014 2:08 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 587, Visits: 2,533
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: Today @ 5:38 AM
Points: 5,079, Visits: 11,863
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.
Post #1592946
Posted Wednesday, July 16, 2014 2:15 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 587, Visits: 2,533

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: Today @ 5:38 AM
Points: 5,079, Visits: 11,863
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.
Post #1592960
Posted Wednesday, July 16, 2014 5:39 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:28 AM
Points: 3,990, Visits: 3,427
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 12,963, Visits: 10,735
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1593006
Posted Wednesday, July 16, 2014 5:51 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:24 AM
Points: 962, Visits: 2,190
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 12,963, Visits: 10,735
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1593012
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse