Is this column unique or non unique

Database
Enthusiast

Is this column unique or non unique

Hi All, 

 

I have below table 

 

Create set table xyz.tablename ,

(columname 1 )

primary index (columnname1);

 

So what type of primary index  does the optimizer think unique or non unique ?


Accepted Solutions
Highlighted
Senior Apprentice

Re: Is this column unique or non unique

Hi,

 

They work the same as they've always worked in TD.

 

A PRIMARY KEY (PK) is a logical modelling component, whereas indexes are physical modelling components. A PK is unique, not null etc. Since supporting the syntax in  CREATE TABLE statements, TD has always implemented PK's as a unique index, either Primary or Secondary depending on other factors.

 

The 'K' value in the DBC.Indices view simply means that this index was defined using the PK syntax as opposed to 'index' syntax.

 

It is handled / processed like any other unique index - the slight difference being that the PK column(s) must be defined as NOT NULL (indexed columns do not have to be NOT NULL).

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
4 REPLIES
Senior Apprentice

Re: Is this column unique or non unique

Hi,

Do a 'show table' or 'help index'. On TD16.20 this comes back as a NUPI.

You haven't defined the index as unique - although I accept that it has to be unique because it is a single column in a SET table.

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Is this column unique or non unique

thank you !

 

We have tables with PRIMARY KEY mentioned instead of primary index .

 

When i do help index PK is considered as unique Primary index but when I saw in the INDICIES table the index type is K.

 

so how does a primary Key work in 16.20. ?

 

Immediate help is  appreciated  !!

 

 

Highlighted
Senior Apprentice

Re: Is this column unique or non unique

Hi,

 

They work the same as they've always worked in TD.

 

A PRIMARY KEY (PK) is a logical modelling component, whereas indexes are physical modelling components. A PK is unique, not null etc. Since supporting the syntax in  CREATE TABLE statements, TD has always implemented PK's as a unique index, either Primary or Secondary depending on other factors.

 

The 'K' value in the DBC.Indices view simply means that this index was defined using the PK syntax as opposed to 'index' syntax.

 

It is handled / processed like any other unique index - the slight difference being that the PK column(s) must be defined as NOT NULL (indexed columns do not have to be NOT NULL).

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Is this column unique or non unique

thank you !! Dave this is very helpful.