Primary Key

Question

Post reply

Primary Key

Austin_123

SSCommitted

Points: 1648
More actions
December 25, 2007 at 10:21 pm

#201950

Hi All,
How many primary keys can i create in a table. I mean total number of primary keys in a table.....
Regards,
Austin

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

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 SSChampion Points: 11815 More actions · Answer 2

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 - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

You can have multiple columns in your primary key as well, so it could be a compound key instead of a single column.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Satheesh-480895 SSC Enthusiast Points: 100 More actions · Answer 5

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 SSChampion Points: 11316 More actions · Answer 6

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).

[font="Arial Narrow"](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.[/font]

Durai Samuel Mr or Mrs. 500 Points: 551 More actions · Answer 7

For full information about primary key

please view the below link

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Phil Parkin SSC Guru Points: 246985 More actions · Answer 9

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel