SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Primary Key


Primary Key

Author
Message
Austin_123
Austin_123
Mr or Mrs. 500
Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)

Group: General Forum Members
Points: 532 Visits: 457
Hi All,

How many primary keys can i create in a table. I mean total number of primary keys in a table.....

Regards,
Austin
ChiragNS
ChiragNS
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3363 Visits: 1865
You can have only 1 primary key for a table. Primary key can constitute a single column or can have multiple columns.

If uniqueness of data is what you want you can look at unique indexes.

"Keep Trying"
David McFarland
David McFarland
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1165 Visits: 214
Maybe I'm misunderstanding you, but if not, the answer is one. A table can only have a single primary key constraint.

ETA: To be quite specific, it can also have zero, so technically, the answer is zero or one.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61939 Visits: 19100
You can have multiple columns in your primary key as well, so it could be a compound key instead of a single column.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85222 Visits: 41078
Although you can have only 1 Primary Key (hence the word "Primary"), you can, in fact, have multiple UNIQUE keys in the form of UNIQUE indexes. Keep in mind that a Primary Key cannot have any NULLs whereas a UNIQUE key can have 1 (easily taken care of by a NOT NULL constraint).

Those UNIQUE keys can be used in conjuction with Foreign Keys (which is what I think the basis of this question is really about).

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Satheesh-480895
Satheesh-480895
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 94
Hi,

Table can have only one primarykey, this may contain one or more than one column. if you want you can create uniquekeys more than one.

Thanks
Satheesh
Mauve
Mauve
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1736 Visits: 2054
The other "unique" constraints are often called "Alternate Keys" (AK in the IDEF1X data modeling notation).

Consider the following simple example:

Countries, per the ISO 3166 specification can be identified by:
- Name (e.g., "United States of America")
- 2-character code (e.g., "US")
- 3-character code (e.g., "USA")
- 3-digit number (e.g., "840")

Each of which must be unique. So a table of Countries would have one Primary Key (PK) and at least 2 other alternate keys (unique constraints) that can provide indexed lookups via the other "key" values.

Which one to use as the primary key would depend upon the application. But, in this example case, I'd use the 3-character code as the primary key (as it is the most common usage).


(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Durai Samuel
Durai Samuel
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 65
For full information about primary key

please view the below link

http://www.dotnetbites.com/Primary-key-Sql-Server
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25924 Visits: 17519
Durai Samuel (8/1/2013)
For full information about primary key

please view the below link

http://www.dotnetbites.com/Primary-key-Sql-Server


Please note that this is thread is 6 years old. I did however head over and read the link which I assume must be your blog. You have some VERY incorrect information there.


Now Run a Select Query, you would probably find the difference in the result where you have the Primary Key Specified. The select Result will be sorted out in ascending order, where as in the Table which doesn’t have the Primary key will not be in the sorted order. This is the Quality of the Primary Key.


This is 100% incorrect. While it is likely that your select will return the rows in order of the primary key there is absolutely no guarantee that this will happen or that this behavior will continue in future releases. There is one and ONLY one way to ensure the order of your data, add an ORDER BY clause to your query.

In addition to incorrectly stating that a primary key has anything to do with the order of retrieval you also are making an assumption that somebody trying to learn this could be very confused by. It is obvious that you are making the assumption that your primary key is also your clustered index. This is the default for a primary key but is NOT required. The ordering behavior you are describing is actually based on the clustered index and not the primary key.

Need some proof that the ordering is not going to work? Check out this article. http://blogs.msdn.com/b/conor_cunningham_msft/archive/2008/08/27/no-seatbelt-expecting-order-without-order-by.aspx

It will walk you through this fallacy of primary key (clustered index) ordering and show you exactly how to prove that it is inaccurate.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18277 Visits: 20409
I second Sean's comment.

The contents of the page linked to by Durai Samuel should not be relied on as they contain multiple incorrect statements relating to the nature and behaviour of PKs.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search