Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2005

    Question Unanswered: Performance with Composite PK?

    Hello All,

    I'm looking for some advice on performance tuning (if it needs to be done) with a child table.

    The Child table has only two columns: SET_ID, PID. However, I don't want a unique ID generated in a separate column. Instead, I wish to just create a primary key based on both of these columns. The SET_ID is a foreign key from the SET table (in that table, SET_ID is the unique primary key). Each item (PID) in this child table is associated with a particular set. The PIDs can reoccur throughout the table, as well as the SET_ID. However, I do not wish to have duplicate row values.

    Is it wise to do a double column primary key in this case? I will be performing many UPDATEs on the table through VB ( [databaseConnObj].Update([datatableInMemory]) ). I just want to make sure that I won't be experiencing any bad performance issues before proceeding with this design. If you can think of any better way to do this, I would appreciate the input!



  2. #2
    Join Date
    Jun 2004
    Liverpool, NY USA
    Don't create a primary key on the table, create a composite UNIQUE key with non null on both columns, this will allow you to change the records at will and still be able to access the specific row.

    create table my_table (SET_ID number not null, PID number not null);

    create unique index my_table_u1 on my_table(set_id,pid);
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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