While creating a table if i specify both the Primary Index and Primary Key on the same column the table cretion fails.
CREATE TABLE temp4243426
name INT NOT NULL,
name2 INT NOT NULL,
PRIMARY KEY (name)
Whereas if we create the table without Primary Key and then alter and add Primary Key it works fine? Any specific reason for this? Wont it create a USI if we alter and add Primay Key later?
When you specify Primary Kay keyword inside create table, Teradata will be default consider as a Unique Primary Index.
So in addition, if you specify another non unique primary index on same column, it will throw an error.
In your case, since name is already being considered as an UPI candidate, you can not define NUPI on same name column.
Executing below sql ddl will create name as an UPI:
This seems to be a bug, a USI on top of a NUPI in this case is totally stupid.
The only case where this might be useful is a partitioned table where the partitioning column is not part of the PI (and then it's allowed in CREATE TABLE, too).