Post reply

Primary Key

  • 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

  • December 25, 2007 at 10:25 pm

    #762640

    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"

  • December 25, 2007 at 10:28 pm

    #762642

    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.

  • December 26, 2007 at 9:54 am

    #762723

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

  • December 26, 2007 at 10:57 am

    #762745

    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)

  • December 27, 2007 at 6:37 am

    #762914

    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

  • December 27, 2007 at 7:46 am

    #762939

    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]

  • August 1, 2013 at 12:41 pm

    #1637909

    For full information about primary key

    please view the below link

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

  • August 1, 2013 at 1:03 pm

    #1637923

    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/

  • August 2, 2013 at 12:24 am

    #1638048

    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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

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

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