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.

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

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

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)

thanks

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?

primary key = date + time + register number + product code

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?

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

.... 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

That's what happens when you feed a troll, Rudy.

Somasundaram not troll. Somasundaram - DBA!!

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.

good point

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