Results 1 to 10 of 10

Thread: Primary Key

  1. #1
    Join Date
    Jun 2010
    Posts
    28

    Unanswered: Primary Key

    Hi All

    Can someone tell me the real purpose of a Primary Key?

    1. I mean if its to define unique values then I don’t need to add a Primary Key - instead I can just add an Unique field (AutoNumber)
    2. If its to use it as a key for relational tables then really I can still create relationships between tables without needing to define a Primary Key – theoretically speaking as I can create a relationship between 2 field that doesn’t have to be unique, It just tell the computer two match 2 field that have similar values in common
    3. Relationships can be made using any field (doesn’t have to be a Primary Key field)

    So what is the purpose?

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. the difference is that the PK has to be NOT NULL while the UK doesn't

    2. yes, you can define a relationship on either a PK or on a UK

    3. if you don't use the PK you must use a UK
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Guys, correct me if I am wrong here because most of my knowledge has been gained by the seat of my pants, but my understanding is that the PrimaryKey also forces the physical order of the data on the disk (clustering), a characteristic that you can leverage for the purpose of speeding-up your application.

    That PrimaryKey is then used (going out on a limb here) as the base index for every other index against that table.

    Without a PrimaryKey (and here's where I am hanging-on to the last leaf on that last branch of the limb), I assume that any UniqueIndex has to use the simple sequential ordering of the data in order to cross-reference the location of the data referred-to by the UniqueIndex.

    Did I fall out of the tree?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, the clustered index does not need to be unique.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Quote Originally Posted by blindman View Post
    No, the clustered index does not need to be unique.
    But the PrimaryKey must be clustered, correct?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Ken, I believe if you just define a Primary Key, it will automatically create a clustering index. But it doesn't have to be that way. You can define a clustering index on non-unique columns. However, SQL Server will add a unique 4-byte uniqueifier column to the table and a unique value to each row. These are internal items that you can't see or use.

    However, I think Primary key and Clustering Index are the same in most situations.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you DBAs are done with your cluster-f... i mean, finished discussing the physical implementation details of indexes, how about addressing the major part of the original poster's question, regarding relationships?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by shamas21 View Post
    theoretically speaking as I can create a relationship between 2 field that doesn’t have to be unique, It just tell the computer two match 2 field that have similar values in common
    ...yes, but you risk the very real possibility of creating a cross-join. At least one of the tables should joined on a unique key.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    at this point i need to ask for clarification from the original poster about what he meant by "create a relationship between 2 field that doesn’t have to be unique"

    blindman interprets this a joining two tables in a query on any two columns, which of course you are allowed to do whether a relationship exists or not

    my impression is that to create a relationship means to declare a foreign key

    so what are you really asking?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    More likely he(she/it) means to keep the relationships via the application, rather than on the database side. At least that is my reading of it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •