1st column automatically indexed?

[chinmark]

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?

[FGroenen · Accepted solution]

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.

[chinmark]

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 :-)

[FGroenen]

NOPI = No Primary Index (what you want)

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