Choosing Primary Index for a table

Database
Enthusiast

Choosing Primary Index for a table


1) The table is a SET table.Product id and Brand is same for both the records, it should still insert into the table as the Active_Dt is different correct?

CREATE SET TABLE CONSUMER_PRODUCT

(

SEQID DECIMAL(27,0),

PRODUCT_ID VARCHAR(20),

PRODUCT_NAME VARCHAR(30),

BRAND_ID VARCHAR(20),

BRAND_NAME VARCHAR(30),

ACTIVE_DT DATE,

ACTIVE_FLAG CHAR(1),

END_DATE DATE

)

PRIMARY INDEX(PRODUCT_ID,BRAND_ID)

Result

001 P123 Herbeck B123 Belista 2015-03-14 Y 

002 P123 Herbeck B123 Belista 2015-02-12 Y 

2) The table is a SET table.Product id and Brand is same for both the records, it would fail since it is exact row duplicates correct?

CREATE SET TABLE CONSUMER_PRODUCT

(

SEQID DECIMAL(27,0),

PRODUCT_ID VARCHAR(20),

PRODUCT_NAME VARCHAR(30),

BRAND_ID VARCHAR(20),

BRAND_NAME VARCHAR(30),

ACTIVE_DT DATE,

ACTIVE_FLAG CHAR(1),

END_DATE DATE

)

PRIMARY INDEX(PRODUCT_ID,BRAND_ID)

Result

001 P123 Herbeck B123 Belista 2015-03-14 Y 

002 P123 Herbeck B123 Belista 2015-03-14 Y 

3) The table is a SET table.Product id and Brand is same for both the records.

   This would fail as the Product_id and Brand id are defined as UNIQUE PRIMARY INDEX and hence not allowed correct?

CREATE SET TABLE CONSUMER_PRODUCT

(

SEQID DECIMAL(27,0),

PRODUCT_ID VARCHAR(20),

PRODUCT_NAME VARCHAR(30),

BRAND_ID VARCHAR(20),

BRAND_NAME VARCHAR(30),

ACTIVE_DT DATE,

ACTIVE_FLAG CHAR(1),

END_DATE DATE

)

UNIQUE PRIMARY INDEX(PRODUCT_ID,BRAND_ID)

Result

001 P123 Herbeck B123 Belista 2015-03-14 Y 

002 P123 Herbeck B123 Belista 2015-01-26 Y

4) Can we define a table like below with both unique and non unique index

CREATE SET TABLE CONSUMER_PRODUCT

(

SEQID DECIMAL(27,0),

PRODUCT_ID VARCHAR(20),

PRODUCT_NAME VARCHAR(30),

BRAND_ID VARCHAR(20),

BRAND_NAME VARCHAR(30),

ACTIVE_DT DATE,

ACTIVE_FLAG CHAR(1),

END_DATE DATE

)

UNIQUE PRIMARY INDEX(SEQID)

PRIMARY INDEX(PRODUCT_ID,BRAND_ID)

5) Last question.

While defining a combination of Primary idex, how do we have to decide which columns would be apt to be defined as primary index? What are the factores to be considered? 

2 REPLIES
Enthusiast

Re: Choosing Primary Index for a table

Hi Friends,

Can you comment on the above? Thanks

Regards

John

Senior Apprentice

Re: Choosing Primary Index for a table

Q1/Q2/Q3: yes, of course

Q4: no, of course, there's only one PI per table (think of it as a kind of clustered index)

Q5: columns used for joins (in best case also used in Where) if the distribution is ok (usually up to a three to four digit number of rows per PI value) and it's not heavily updated.

You'll find all the anserws in the Database Design manual.