Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250

    Unanswered: index and primary key

    By defining a numeric field in table as primary key, will the table be indexed on that particular field?
    Cheers....

    baburajv

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, a primary key always gets an index, that's how the database system determines if a value exists already or not (for uniqueness)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    am extending my qn a littl bit

    suppose the table has the following structure

    myTable
    (
    myPK bigint identity (Primary key)
    myUniqNo bigint
    myName varchar (50)
    )

    can i create an index on myUniqNo, if myUniqNo is unique..
    Cheers....

    baburajv

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can create an index on almost any column, whether it is unique or not. You can create a unique constraint or a unique index on a column if there are no duplicate values in the column.

    I'd recommend using a constraint instead of an index unless there is some specific, compelling reason for using the index.

    -PatP

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes you can

    but then, if myUniqNo is going to be unique, why do you want an IDENTITY column as the primary key?

    and by the way, why bigint? are you planning on having over 2 billion rows?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    but then, if myUniqNo is going to be unique, why do you want an IDENTITY column as the primary key?
    A surrogate key for the existing surrogate key? That way they can allow updates to their existing column?

    Ow, ooo, ow! Quit throwing things, that hurts!!!

    -PatP

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes, but then they can change the "key" without having to cascade all of the updates...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Pat Phelan
    I'd recommend using a constraint instead of an index unless there is some specific, compelling reason for using the index.
    Can you tell us why you'd recommend that? Do you know that when you create a unique constraint you implicitly create a unique index?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes I'm sure Pat knows....I think Pat is spouting party line...M$ reccomends that as well...

    Never could figure out why...or maybe we did and I forgot...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    About the only thing that a unique constraint has going for it as opposed to a unique index is that you can have a foreign key dependent on a unique constraint. After that, it gets a bit fuzzy. Does anyone know of any articles where the order of checks is done for an insert in SQL Server? For example, are check constraints checked before foreign keys are? Or do some triggers fire before computed columns are generated? That sort of information might give some insight.

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    When UNIQUE constraint gets created, a UNIQUE index gets created at the same time with the same name. If you drop the constraint the index gets dropped with it, also implicitly. Trigger never gets to execute if uniqueness is violated either due to constraint or unique index.

    Talking about differences, the only one I see is that while constraint is very strict in respect to controlling RI, unique index can be altered in such a way, where in a batch of 100 rows attempted to be inserted there is 1 duplicate row, 99 will be successfully inserted. Nothing can be done to accomplish the same with unique constraint. That's why MS (and Pat) recommend using constraints over indexes.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    Yes I'm sure Pat knows....I think Pat is spouting party line...M$ reccomends that as well...
    Not hardly... The only time I "spout party line" is when I'm actually at the party.

    Creating a constraint creates metadata. Some programs use metadata now, and more will in the future. Metadata is an important stepping stone toward getting real "relational algebra" tools (especially things like OLAP), which will make life lots easier for everyone as they become more readily available.

    -PatP

  13. #13
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    dont forget the null.
    you have to mention the 1 null...

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    according to this thread there is a dodgy way around the "only 1 null in a unique index" problem, but i haven't confirmed that it works, i just bookmarked it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Pat Phelan
    Not hardly... The only time I "spout party line" is when I'm actually at the party.

    Creating a constraint creates metadata. Some programs use metadata now, and more will in the future. Metadata is an important stepping stone toward getting real "relational algebra" tools (especially things like OLAP), which will make life lots easier for everyone as they become more readily available.

    -PatP

    Good Point...so where's the party?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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