How to create multiple primary indexes for a table?

Question

I'm curious if creating a primary key for multiple columns uses the same format as for creating a primary index for multiple columns like below? Let say that I want to have two primary indexes for the table below, is it correct?

CREATE TABLE sample_1
 (col_a INT
 ,col_b INT
 ,col_c INT)
 PRIMARY INDEX (col_b, col_c);

Is this for SQL Server? You cannot have two primary indexes. [Check out this question](https://stackoverflow.com/questions/217945/can-i-have-multiple-primary-keys-in-a-single-table). — Andrew, Mar 03 '20 at 13:49
And why did you not even mention that? It should be a tag. I just added it. — Andrew, Mar 03 '20 at 16:32
In Teradata, primary index and primary key are not interchangeable - see the [documentation](https://docs.teradata.com/reader/_a1eqWqCcebCUBcUpkaVvA/1OYHYeXCiSlRNueGWPfB4g) — Andrew, Mar 03 '20 at 18:03
So if the column col_a is to be a primary key and a UPI, I should only declare Primary Key (col_a) which would automatically indicate it to be both...am i right? What about if it is to be a Primary Key, but a NUPI? How should I declare it since it would not allow to be declared twice one for the PK and the other for NUPI... — user234568, Mar 04 '20 at 03:05

score 0 · Answer 1 · answered Mar 03 '20 at 18:04

You can't have two Primary Indexes in Teradata, a PI is similar to a Clustered Index in other DBMSes. There can be only one!

PK is defined as part of the column definition:

CREATE TABLE sample_1
 (col_a INT
 ,col_b INT NOT NULL
 ,col_c INT NOT NULL
 ,PRIMARY KEY (col_b, col_c)); -- UPI

In this case it will be implemented as a Unique Primary Index.

If you add both PI and PK, the PK will be implemented as Unique Secondary Index, e.g.

CREATE TABLE sample_1
 (col_a INT
 ,col_b INT NOT NULL
 ,col_c INT NOT NULL
 ,PRIMARY KEY (col_b, col_c)) -- USI
PRIMARY INDEX (col_a);        -- NUPI

How to create multiple primary indexes for a table?

1 Answers1