Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Oct 2009
    Posts
    24

    Unanswered: primary key for table db2

    i take database class before. teacher say all tables must have primary key. now i use db2. db2 does not require primary key - is this wrong in db2? sorry my bad englis.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Somasundaram1
    db2 does not require primary key - is this wrong in db2?
    yes, some people would say that it is wrong...

    ... but most other database systems (mysql, sql server, etc.) don't require a primary key either

    nevertheless, your teacher is right, you should never create a table without declaring a primary key yourself

    and your english is fine

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

  3. #3
    Join Date
    Oct 2009
    Posts
    24
    so if no key in table then i just make one up - surrogate key? yes? your ideas are good and helpful - i will buy your book

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Somasundaram1
    so if no key in table then i just make one up - surrogate key? yes?
    a surrogate key only under one of two conditions --

    1. if the natural key is unweildy (e.g. multiple columns)

    2. if there really, really is no natural key (but then you probably didn't look hard enough)

    Quote Originally Posted by Somasundaram1
    your ideas are good and helpful - i will buy your book
    thanks
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2009
    Posts
    24
    i work project for cash register. cash register give us file every day: field 1 - date, field 2 - product upc. we want to load file and cound how many each product sold every day, every week, every month. so file look this:
    .
    2009-01-01, 1_ltr_of_milk
    2009-01-01, 1_loaf_of_bread
    2009-01-01, 1_ltr_of_milk
    .
    query look like "select product,count(*) from table where sale_date='2009-01-01' group by product"
    .
    what primary key here?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Somasundaram1
    what primary key here?
    primary key = date + time + register number + product code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2009
    Posts
    24
    my dear friend book writer! you no understand - we dont have time or register number , nor do we care what they are. all given to us is date and upc. we will be perfectly happy to load this in table and count how many sold in a day. why do we need primary key?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Somasundaram1
    why do we need primary key?
    you don't

    you did not ask me if you need a primary key

    you asked me what it would be, and i gave you the minimum (4 columns) that i thought would be required
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2009
    Posts
    24
    Quote Originally Posted by r937
    you should never create a table without declaring a primary key yourself
    Quote Originally Posted by r937
    you don't <need a primary key>
    .... a contradiction it seems

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Somasundaram1
    .... a contradiction it seems
    yes, but ~you~ manoeuvered us into it

    if it makes youi feel better, you can create a surrogate PK for your two-column cash register table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by r937
    yes, but ~you~ manoeuvered us into it
    That's what happens when you feed a troll, Rudy.
    ---
    "It does not work" is not a valid problem statement.

  12. #12
    Join Date
    Oct 2009
    Posts
    24
    Somasundaram not troll. Somasundaram - DBA!!

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Somasundaram1
    .... a contradiction it seems
    I don't see any contradiction: the first sentence states what you should do while the second states what you must do (i.e. nothing for primary keys).

    Just one other aspect: in the context of relational database systems, there is nothing special about a "primary key". It is just a unique constraint with a different name (and the default target for referential constraints). Therefore, I would rather say that each table should have a unique constraint.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by stolze
    Therefore, I would rather say that each table should have a unique constraint.
    good point
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    i can not read this nonsense!

    tables in DB2 don't necessarily need PKs. Decision to create PKs is driven by business requirements. If you need to enforce uniqueness you can and should use unique indexes. PKs created in 2 cases only:

    - referential constraint
    - when you need to do import with insert_update
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

Posting Permissions

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