Indexes - The water is gett''n muddy
-
Hey everyone,
I'm probably making more out of this than I need to at this point in my education but I really would like to have a solid (correct) understanding of what's going on.
Basically, what is the difference between a primary key and an index? I know at first read, this probably looks like a very dumb question and maybe it is.
As I understand thing, and primary key is a constraint placed on a table which indicates that this column (or columns) can not be duplicated in the table. A clustered index can be created to physically sort the rows of data in a table.
My confusion came the other day when I tried to create a clustered index for a table that had a primary key and in doing so I received an error that said the index could not be created because one already existed and it referrenced my primary key "index"
So, if I define a primary key in a table does that mean that I just created a clustered index for said table?
Again, I'm sure I'm missing something very basic here so any information / direction would be greatly appreciated.
Thank you,
Bob
-
A primary key is a unique index (hence it is a constraint).
A clustered index is one that determines the way that the data is sorted (and in fact stored) in the table. If you create a primary key on a table that has no clustered index, SQL will make it clustered by default unless you specify otherwise.
You can have multiple indexes against the same columns in sql, including unique indexes. You can only have a maximum of one clustered index per table, and I suspect this is the error you were getting.
Matt.
-
I agree with matt. In a relational database, a Primary Key is simply a unique (hence non null) row identifier. Indexing is an implementation issue. But since the RDBMS must check that the PK is unique each time a record is added, and since relational joins will hang on posting PKs to other tables, it makes sense to index the PK (and usually FK) column. The PK index defaults to clustered, but need not be if data is regularly selected and ordered by a different column: it might then be better for that column to have the clustered index.
-
I think the distinction between a primary key and a unique index is that a primary key is a logical entity for defining how data may be uniquely referenced in a table and an index is a physical one used for quickly looking up data. Hence a PK will create a unique index as it is expected that this will be used to look data up frequently. The PK then becomes more relevant when you consider several tables related to one another (by PKs and FKs).
A proper DBA type may correct me though!
-
A simple, but I think "clear" way to look at it:
A unique index is the physical implementation that SQL Server uses to enforce a logical PK.
(SQL Server defaults to a unique clustered index if not specified, but can use clustered or non-clustered index - the unique'ness is necessary whichever index type you choose)
jg
-
A PK is a the way of uniquely indentifying each row within a table. It is implemented in SQL Server by means of a unique index. This can be clustered or not.
An index is a way or ordering the rows in a table. A nonclustered index does this logically, while a clustered index does this physically.
Do I get the job?
-
HIRED!
That helps a LOT. I've always thought of a PK as a way to ensure that a row is unique and all of the responses (awesome as they are) kept bringing up the clustering part of it. I do understand that was part of my original question but I just wanted to make sure I wasn't over thinking things, which I tend to do a lot.
Indepth details are great but I was in search of the 'bottom-line' answer which is exactly what you gave...
Thank you all for the information!
Thanks Steve,
Bob
-
I'd add one more bit - a primary key can not have null values, but a unique index can have one null.
-
ANSWER: Nope, you don't get the job!
REASON: Not enough Posts on this site! [Smile]
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply