Primary key and Primary index on a same column

Database
Enthusiast

Primary key and Primary index on a same column

Hi All,

    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)
)PRIMARY INDEX(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?

4 REPLIES
Teradata Employee

Re: Primary key and Primary index on a same column

Hello Sachin,

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:

CREATE TABLE temp4243426

(

  

name INT NOT NULL,

name2 INT NOT NULL,

PRIMARY KEY (name)

)

Regards,

Chinmay Athavale

Enthusiast

Re: Primary key and Primary index on a same column

Hi Chinmay,

      Thanks for the response :-) 

But Primary Key will be implemented as UPI only if there is no Primary Index specified in the DDL right?? In my case i am explicitly specifying that.

If PI is already there then the PK will be implemented as SI right? So here it should create an SI.

But the actual question is that why its not allowing that during the initial creation itself whereas it allows it via an alter statement! Any specific reason?

Pelase correct me if my understanding is wrong.




Re: Primary key and Primary index on a same column

Hi i'm following this post, could someone give an answer to barani_sachin last comment?

Junior Contributor

Re: Primary key and Primary index on a same column

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