I am new in Teradata and facing an issue in Primary Key sequences. Below is the sample.
I am creating a table with composite primary keys and providing the primary keys as below:
constraint PK_ACCT_DESCRIPTOR primary key (V, A, D, De, Des)
But after running the create table statement , When I am runnning Show Table statement it is showing primary key in below sequence
CONSTRAINT PK_ACCT_DESCRIPTOR PRIMARY KEY ( A ,D,De ,Des ,V ))
Also, it is the same case in DBC.INdices too when I am querying it, It is giving the column position in the same order as show table statement shows.
I am not sure how the sequence of keys got changed while creating the table.
I am using Teradata V15.
Any help is highly appreciated..
Firstly, I think you'll find that the order of columns in indexes is determined by the order of those columns in th etable definition statement (not sure but I think that is what happens).
More importantly, why do you think this matters? Why does this matter to you?
It doesn't affect how Teradata stores or processes the data.
It doesn't affect performance.
You are talking about a Primary Key, so the combination of values is unique. Just thinking about a 2-column PK of col1 and col2:
- row#1 col1 = 1 and col2 = 2
- row#2 col1 = 1 and col2 = 2
Whether the index is described as (col1,col2) or (col2,col1) these two rows have duplicate PK values and therefore trying to add the second row will cause a PK violation. Again, th eorder in which the columns are described, shown in the table etc. does not matter.
Please remember that the way Teradata builds and uses indexes is quite different to some other DBMS's.
Also remember that if you define a PK, Teradata will build an index (a "subtable" - a dataset). As Dave said, the order of columns in this index doesn't matter because it can only be used for lookup (mainly used internally to maintain constraints). It is not a default sequence as it may be in other "sequential" database systems like Oracle. Most people do not worry about deining PKs in Teradata. The Primary Index is far more important and not necessarily the same thing as the PK; also the PI does not create a dataset to store index values like other indexes: it is just a hashing algorithm.