All Forums Database
johnsunnydew 43 posts Joined 09/14
13 Mar 2015
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? 

johnsunnydew 43 posts Joined 09/14
14 Mar 2015

Hi Friends,
Can you comment on the above? Thanks
 
Regards
John

dnoeth 4628 posts Joined 11/04
16 Mar 2015

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.

Dieter

You must sign in to leave a comment.