January 27, 2015 at 12:34 pm
I am trying to learn more about primary keys
Please check the screen print in the attachment
Questions for you please
1.) Would you agree if I said that the MPOG_CASE_ID is the one and only primary key in this table ?
2.) I thought the PRIMARY key can not have duplicate values .... Correct me if you disagree ?
January 27, 2015 at 12:49 pm
No, you're confusing primary keys with the PRIMARY filegroup.
Primary Keys need to be unique. PK_AIMS_Preop index is the index that supports your PRIMARY KEY constraint that should be shown in the dataset below but can't be seen in the image. This means that the PK is on MPOG_Preop_Note_ID column.
PRIMARY filegroup is the default filegroup created when you create a new database. It only defines storage options and has nothing to do with the data itself.
January 27, 2015 at 1:18 pm
I thought you could only have one clutstered key and that one should be the primary key ? Right/Wrong ?
Sorry, my bad vision, now I see in the image it clearly says MPOG_PREOP_Note_ID is the primary key.
Bow I thought the PROMARY key has always go to be clustered ? Right ? Please explain
January 27, 2015 at 1:37 pm
mw112009 (1/27/2015)
I thought you could only have one clutstered key and that one should be the primary key ? Right/Wrong ?Sorry, my bad vision, now I see in the image it clearly says MPOG_PREOP_Note_ID is the primary key.
Bow I thought the PROMARY key has always go to be clustered ? Right ? Please explain
Do you see the picture you posted? Do you think that SQL Server is lying to you?
If I say that "primary key is always clustered", are you going to trust me or SQL Server on that?
January 27, 2015 at 1:39 pm
mw112009 (1/27/2015)
I thought you could only have one clutstered key and that one should be the primary key ? Right/Wrong ?Sorry, my bad vision, now I see in the image it clearly says MPOG_PREOP_Note_ID is the primary key.
Bow I thought the PROMARY key has always go to be clustered ? Right ? Please explain
You can only have one clustered index. The clustered index is, in essence, the table. It defines how the data is stored in the table.
The primary key defines how you determine a unique row in the table. The primary key does not have to be the same as the clustered index, although it usually is.
I can think of a couple of cases where you might not want the primary key to be the same as the clustered index. For example, you could have the primary key be a GUID, but if you're not using a sequential GUID in your table, then you will have rapid fragmentation if you make the GUID a clustered index. In general, you want the clustered index to be something that is sequentially always increasing.
January 27, 2015 at 1:39 pm
The primary key is usually the same as the clustered index but that's not a rule. They're both independent as the process to define them is different. The primary key is defined by business rules to ensure that it is a unique identifier of each row and is the key used for references/relationships with other tables through the use of Foreign keys (a foreign key can only reference a primary key).
Clustered indexes are defined by the data characteristics. Some will tell you that it must be narrow and ever-increasing, and people tend to use an unnecessary identity column which won't bring any benefits (or maybe it's exactly what you need).
For example, our PKs for orders tables are defined by 3 columns (company, branch, order) but our clustered index is better to be set on the date column.
This is a wide topic, but I hope that I gave you the basic information.
You could read more in here: http://sqlinthewild.co.za/index.php/2011/11/11/sql-university-advanced-indexing-indexing-strategies/
January 27, 2015 at 1:48 pm
Good Explanation. I got my answer!
So the final conclusion--> PRIMARY does not have to be clustered
January 28, 2015 at 3:07 am
Luis Cazares (1/27/2015)
The primary key is usually the same as the clustered index but that's not a rule. They're both independent as the process to define them is different. The primary key is defined by business rules to ensure that it is a unique identifier of each row and is the key used for references/relationships with other tables through the use of Foreign keys (a foreign key can only reference a primary key)...
Actually, in SQL Server a foreign key can also reference a unique index that is not a primary key. I am not saying it is desirable, but it is possible
The code below shows that you can create a foreign key reference to a unique index that is not even a candidate primary key, since it is nullable.
create table x ( xid int null )
create unique index x_xid on x (xid)
create table y ( yid int not null , xid int null )
alter table dbo.y add constraint fk_y_x
foreign key ( xid ) references dbo.x ( xid )
drop table y
drop table x
January 28, 2015 at 8:49 am
Sorry, my bad.
Thank you for the correction Michael and the note about not being desirable.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply