Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    May 2006
    Posts
    65

    Unanswered: Forcing Primary Keys

    Hi all,

    As our DB has no primary keys or indexes ive taken a copy of all populated tables and tried to force primary keys within a new DB.

    the problem is all off the tables have multiple datasets within them, a dataset for each year. This causes all instances of ID numbers to not be unique as they are replicated for every year they are active.

    Its a school database so a student who has been here for 3 years will have 3 instances of his ID number, one for each years' data set.

    So how do i force primary keys if there is no unique identifier? ive been highlighting both data set and ID columns and setting that combination as the primary key.

    Essentially i need to analyse the relationships between the tabls in a diagram and also run some speed tests to see how fast the db works when it has indexes and primary keys.

    the reason im writing is that ive done this on ten tables and with another 160 to do im just checking im doing the right thing?



    greg

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Create a composite primary key of student ID and year number.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    May 2006
    Posts
    65
    thought so,
    ta

    greg

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Why do you keep enrollment info (a record for each year of enrollment) in the master table? StudentID should be the only PK in StudentsMaster, and Enrollment should have StudentID as FK.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Sep 2003
    Posts
    364
    Could you create views for each year and put a unique index on each view?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, you CAN.
    No, you SHOULDN'T.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    May 2006
    Posts
    65
    rdjabarov its not my design, its just the way the company programmed it, its a very bad system, ive alreday had to weed out 400+ tables that werent being used, and it seems instead of introducing foreign keys to child tables they used the studentId and the SetId,

    peterlemonjello, i didnt know you could do that, well at least in sql server 2000, thought it was a 2005 feature...ill look into that

    blindman, i had read it wasn't a good idea...ill think of an alternative

    greg

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Where ever did you read that? Tables need primary keys, and if they don't have a natural unary key then you either create a surrogate key or use a composite key. Creating indexed views would be an odd alternative.
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    May 2006
    Posts
    65
    well this is the thing, im not trying to fix the db so it functions- im just truying to analyse the relationships between tables and see how much faster introducing keys and indexes make my queries run...

    as you can imagine the company released the software with no primary keys and expect it to work but im not about to try and fix there mistakes...its purely for my own use....

    i really cant believe they have released software like this but i have to work with what i inhereted off my predecessor

    greg

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It will run faster if it is indexed, especially clustered indexes as associated with primary keys.
    No need to test this concept....

    What's more, you can throw indexes on it without affecting the functioning of the operation. You cannot throw constraints on the tables (unique indexes, for example, or primary keys) without potentially causing failures in the crappy code which is doubtless used to access the data.
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hmmm, really? I wouldn't be so certain, especially without seeing the database, and without knowing what indexes are to be created and what their definitions are. I've seen "index seek" being more expensive than table scan on multiple occasions (of course because of the poor db and/or query design).
    "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
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Nothing is certain in life except death and taxes, but the benefits of indexing a table come damn close.
    If it's not practically useful, then it's practically useless.

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

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In general that might be true, but then you find a table with 947 indexes, all of which have the first seven columns... Then discover that only the leftmost index column is ever used in queries!

    -PatP

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, yeah,....
    If it's not practically useful, then it's practically useless.

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

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by rdjabarov
    I've seen "index seek" being more expensive than table scan on multiple occasions (of course because of the poor db and/or query design).
    The only time I've seen this is as a result of parameter sniffing. Are there other reasons this can occur? ... actually thinking about it now I guess a poorly chosen index (e.g. low selectivity) and an equally poor plan on the part of the optimiser might cause this.

    BTW - I am probably just being a pedant but if there are no primary keys then there are no relationships. You will not be investigating the relationships of the tables - you will be creating the relationships. I imagine this is not helpful to the issue in hand at all
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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