Results 1 to 6 of 6

Thread: Non PK table

  1. #1
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Red face Unanswered: Non PK table

    Hi all

    When I delete a record from table x, I insert this tansaction in table y as historical data and there is a form based on this table for query.
    but table y has NO primary key, is it ok ?

    thanx in advance

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't know; you tell me.

    Things should be done for some reason. Do you need a primary key on your history table? So create it! You don't need it? Fine, live happily without it.

    My history tables, for example, do have primary keys. I don't want to allow any duplicates in there and I do have queries that frequently use those columns in WHERE clauses.

    So what do YOU think? Did you put all pros and contras together? What do you see?

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If the row in table x had a primary key, why not use the same primary key in y. If you are talking about a one-for-one match, that is what I would do.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    In addition to Bill's thoughts, PK in an archive table *might* need additional column to be part of the PK (this could be, for example, some DATE column).

    Because, if our production table has PK on 'customer_id' and we put all that into the archive table every month, primary key constraitn would be violated the second time we try to archive data. Therefore, archive table should have additional column in PK ('customer_id, archive_date').

  5. #5
    Join Date
    Jan 2004
    Location
    India
    Posts
    113
    Giggs
    U need to think about the reason of setting PK .
    For eg declaring pk would create an index that might help ur query execution fast on such table....if u try look into execution plans of the queries that run against that table ....

    SO u see u r the best person to judge which is a best fit for u .....

    But my xperience says since Historical tables are normally huge should have some indexes.

  6. #6
    Join Date
    Sep 2004
    Posts
    60
    1. I agree with Little foot thought about having composite PK.

    2. If you are not having many value of PK of table x in table Y, You can use PK column of x in Y as normal column & you can create a bit map index. ( keep in mind that PK column of x in y should have few distinct value in comparison to data).
    I used 2nd approch in one Db & it is working fine till now.

    For big dB go for 1st option.

Posting Permissions

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