Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: Is there ANY reason NOT to have a Primary key on a table?

    I took over a db that was developed by an access dude. The invoice Detail table has no PK!!! I want to add one to make my .net life ezier, but the man says that this old dude didnt add pk for a reason,,, I cant think of any ( i was gonna add a Auto#)

    If any one can think of a GOOD reason ill buy u a beer.


    ~M

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Since this is a "Detail" table, most likely the developer did not have info in the table that could be used to generate a primary key without putting in an extraneous unique identifier. I've done the same myself. Since this detail table is keyed to the invoice table thru IT'S primary key it's not necessary other than to speed up some searching ...

  3. #3
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    My 2:

    If the invoice table has a primary key, and there is referential integrity between it and the detail table, and a customer can buy the same item as mutliple line items, you wouldn't really want a primary key.

    Creating an autonumber pk on a subtable (which a detail usu is) isn't an approach I usually use. You can still get multiple items which are usually undesirable. At a min, I would use Invoice# (FK), Customer# (FK), Inventory#(FK), Date / Time of Purchase. Including Qty as a column.

    I would prob use autonum for the Invoice tbl.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  4. #4
    Join Date
    Oct 2003
    Posts
    311
    Originally posted by M Owen
    Since this is a "Detail" table, most likely the developer did not have info in the table that could be used to generate a primary key without putting in an extraneous unique identifier. I've done the same myself. Since this detail table is keyed to the invoice table thru IT'S primary key it's not necessary other than to speed up some searching ...
    Not sure i understand you. Can you clarify pls?

    M~

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Not sure i understand you. Can you clarify pls?
    Reference what HomerBoo wrote ... I mostly agree with him.

    Now what don't you understand about what I said? To clarify: a detail table will have (or can have) multiple records related to a related table which has a primary key (this is a 1 to many relationship). The column(s) relating the tables together (in the detail table) is a foreign key ... Since you really don't act upon a detail table except thru it related parent table there is no real reason to create a unique key for each record in the detail table.

    This help you out?

  6. #6
    Join Date
    Oct 2003
    Posts
    87
    First, we'd have to see the invoice and detail table layouts to answer your question. In 3rd normal form, all tables have a primary key (PK). You must be able to select any one row from a table. Your detail table probably has several columns or combinations of columns that would permit the selection of a single row. These are called candidate keys; pick one to be the PK that represents the access path most often used.

    Now, consider this:

    invoice(invoiceNum PK, dateTimeIssued, customerID FK, . . . )
    invoiceLineNum(invoiceNum PK FK, lineNum PK, itemID FK, . . . )
    item(itemID PK, itemName PK, color PK, weight, . . .)
    customer(customerID PK, customerType FK, . . .)
    Oracle - DB2 - MS Access -

  7. #7
    Join Date
    Oct 2003
    Posts
    311
    ok I understand better. I would like to add a pk to this table becuase I DO need to have one (for .net Dataset Update reasons), but i dont want to put a pk on the table if it will cause problems in anyother area,, inserts (via cut and paste) or any possible way that it could... So i guess that is the real question Could it cause any problems??

    I will use an auto # becuase the detials may at times need to be split so it would be exact same data but 1/2 of the total amount and so if the # is even there is no way to have a unique key.

    Thanks for the lessons guys.

    M~

  8. #8
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326

    Talking

    No problem, where's the beer
    All code ADO/ADOX unless otherwise specified.
    Mike.

  9. #9
    Join Date
    Oct 2003
    Posts
    311
    Originally posted by HomerBoo
    No problem, where's the beer

    Culligans bar main str elmsford ny, im buying see u friday?

    M~

Posting Permissions

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