1st column automatically indexed?

Database
Highlighted
Enthusiast

1st column automatically indexed?

Hello,

I came across an interesting situation when creating a table.  Consider these statements:

 

1) create multiset table test_1 (big_string character large object (32000), id bigint)

 

I get the following error message:

[Teradata][ODBC Teradata Driver][Teradata Database](-5660)Cannot create index on LOB columns.

 

Now, if I change the order of the columns, everything is OK:

2) create multiset table test_1 (id bigint, big_string character large object (32000))

 

Is there any reason why the first column seems to be automatically indexed?  Is there a way to turn it off?

 

 


Accepted Solutions
Junior Supporter

Re: 1st column automatically indexed?

The default behaviour in Teradata, when you do NOT specify a primary index, is to use the 1st column as primary index.

You can change this to create the table as a NOPI table, by changing the DBSCONTROL parameter for this.

 

Teradata Frank, Certified Master
1 ACCEPTED SOLUTION
3 REPLIES
Junior Supporter

Re: 1st column automatically indexed?

The default behaviour in Teradata, when you do NOT specify a primary index, is to use the 1st column as primary index.

You can change this to create the table as a NOPI table, by changing the DBSCONTROL parameter for this.

 

Teradata Frank, Certified Master
Enthusiast

Re: 1st column automatically indexed?

Thank you for the answer.  That solved the error messages regardless of column ordering.

 

By the way, there are options named NOPI as well as NUPI :-)

 

Junior Supporter

Re: 1st column automatically indexed?

NOPI = No Primary Index (what you want)

NUPI = Non-Unique Primary Index (what you got)

 

Teradata Frank, Certified Master