Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Primary Key Expand / Collapse
Author
Message
Posted Tuesday, December 25, 2007 10:21 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:11 AM
Points: 448, Visits: 406
Hi All,

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

Regards,
Austin
Post #436289
Posted Tuesday, December 25, 2007 10:25 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
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"
Post #436292
Posted Tuesday, December 25, 2007 10:28 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, December 8, 2008 8:42 AM
Points: 775, 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.
Post #436294
Posted Wednesday, December 26, 2007 9:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:06 PM
Points: 33,169, Visits: 15,304
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
Post #436396
Posted Wednesday, December 26, 2007 10:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:44 PM
Points: 36,959, Visits: 31,472
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #436426
Posted Thursday, December 27, 2007 6:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 9:00 AM
Points: 76, 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
Post #436634
Posted Thursday, December 27, 2007 7:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 11:03 AM
Points: 1,182, Visits: 1,970
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.
Post #436665
Posted Thursday, August 1, 2013 12:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 1, 2013 12:41 PM
Points: 17, Visits: 61
For full information about primary key

please view the below link

http://www.dotnetbites.com/Primary-key-Sql-Server
Post #1480089
Posted Thursday, August 1, 2013 1:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 13,282, Visits: 12,116
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 Moden's 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)
Post #1480105
Posted Friday, August 2, 2013 12:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:35 AM
Points: 5,037, Visits: 11,777
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1480251
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse