July 16, 2014 at 2:58 am
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.
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
July 16, 2014 at 5:39 am
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.
July 16, 2014 at 5:44 am
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. 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 16, 2014 at 5:51 am
Koen Verbeeck (7/16/2014)
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.
July 16, 2014 at 6:04 am
Raghavendra Mudugal (7/16/2014)
Koen Verbeeck (7/16/2014)
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 16, 2014 at 6:12 am
Koen Verbeeck (7/16/2014)
Raghavendra Mudugal (7/16/2014)
Koen Verbeeck (7/16/2014)
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.
For example:
CREATE TABLE [dbo].[TT2](
[CI] [int] NOT NULL,
[PK] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CI_TT2] ON [dbo].[TT2]
(
[CI] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TT2] ADD CONSTRAINT [PK_TT2] PRIMARY KEY NONCLUSTERED
(
[PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
July 16, 2014 at 6:16 am
Koen Verbeeck (7/16/2014)
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. 🙂
Exactly right - that's the part that made me stop and think for about 1.5 seconds.
July 16, 2014 at 6:24 am
Thank you sknox and Keon. I know this, it a classic method which I doing in my current project for few tables. Actually I was expecting more like NO PRIMARY INDEX command (or any alternate) which we use in Teradata and was thinking there is some similar method which I have missed in MSSQL. 🙂
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
July 16, 2014 at 6:28 am
nimbell (7/15/2014)
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
The question asked: "Is a Primary Key an index?"
If the presence of a Primary Key implies some kind of index is also present, the word also confirms that the index is not the Primary Key.
July 16, 2014 at 6:29 am
Phil almost got me to go for "Yes", but then I realized the terms get conflated because setting the PK in most DBMS's I know automatically creates an index when the table is created/updated.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
July 16, 2014 at 6:51 am
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.
The idea that a table can have only one candidate key is just nonsense - enforcing that rule ensures that certain business rules that define data integrity constraints cannot be represented by any schema that consists only of "properly normalised" tables. Basically any schema all of whose functional dependencies can be represented by keys (candidate and foreign) in EKNF but not in BCNF requires at least one table with two (overlapping) candidate keys in order to ensure that the constraints will prevent data integrity from being violated, so no really competent relational theorist will accept that rule since it would insist on reducing the schema's protection against data integrity failures.
And while I'm being pedantic, I'll out-pedant your question and point out that a primary key is neither an index nor a constraint, but the collected values of a particular set of columns in a particular row; an alternative point of view is that the primary key for the table is the (ordered) set of columns that make up the primary key of each row, which is in some ways a better explanation since with that version there's no need to say that the primary key of each row is defined from each column, and in some ways is worse because on that definition a primary key doesn't identify a row so it isn't a key at all. A primary key constraint is not a primary key, it is the enforcement of the rule that a primary key uniquely identifies a particular row. And even worse, in T-SQL it doesn't even have to be a candidate key, it can be any superkey (which is sometimes - not often - useful and has sometimes - rather more often - led to some appallingly bad schema design). But I still think it's a good question - just that it could have been better.
Tom
July 16, 2014 at 7:01 am
And while I'm being pedantic, I'll out-pedant your question and point out that a primary key is neither an index nor a constraint, but the values of a particular set of columns in a particular row; a primary key constraint is not a primary key, it is the enforcement of the rule that a primary key must uniquely identify a particular row.
Nice, I agree!
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
July 16, 2014 at 7:18 am
arggg... I knew the answer - it's not an index, but creating a primary key does create a corresponding index (if it doesn't already exist). I better cut back on the coffee:w00t:;-)
July 16, 2014 at 7:18 am
This was removed by the editor as SPAM
July 16, 2014 at 8:08 am
arggg... I knew the answer - it's not an index, but creating a primary key does create a corresponding index (if it doesn't already exist). I better cut back on the coffee
Impressive. My only quibble would be that actually SQL Server creates the index even if a suitable one already exists- but that's an implementation issue!
Best wishes,
Phil Factor
Viewing 15 posts - 16 through 30 (of 54 total)
You must be logged in to reply to this topic. Login to reply