Create index on single column got 3519 error

Tools
N/A

Create index on single column got 3519 error

Hi! I'm using SQL Assistant 7.2 and Teradata database V2R6.2 on Windows Server 2003, sp2.
Found something interesting with create index:
1) If an index is created together with unique primary index or unique secondary index, then it does not matter how many columns it covers.
2) If an index is created alone with at least two columns, it would be successful too.
That is, the following simple table will be created successfully
"create table mytable(col1 int, col2 int) index(col1, col2);"

3) However, if an index is created alone with only one column, SQL Assistant will return
"3519: Two indexes with the same columns".

So the following will always fail with 3519 error:
"create table mytable(col1 int, col2 int) index(col1);"
or
"create table mytable(col1 int) index(col1);"

I do not recall there was such restriction in the SQL Reference.
Any help is appreciated.
Regards, Nancy

2 REPLIES
rgs
N/A

Re: Create index on single column got 3519 error

All tables have to have a primary index. That is how it hash distributes the data to all vprocs. If you don’t explicitly define [UNIQUE] PRIMARY INDEX the database will pick the first column in your table definition to be the primary index in your table, which might not be the best choice depending on what data is in the first column. So, when you have only one column in the table it has to be the primary index column. You can’t make that an INDEX column because it is already an index. When you create an index with multiple columns it works okay since the other columns are not part of the primary index.

Create a table with just one column. Then do a show on it. You will see from the show table output that it made that column the primary index. Do the same with a multiple column table. Don’t specify any index and do a show table on that table. You will see that only the first column becomes the primary index.
N/A

Re: Create index on single column got 3519 error

Wow! Excellent! Learnt something new everyday!
Thank you very much!

Regards, Nancy