USI vs Primary key to enforce uniqueness

Database
Enthusiast

USI vs Primary key to enforce uniqueness

Hi ,

We have a composite primary index column and partitioning is done on column whcih is not part of primary index , hence we are not in position to create unique primary index.

we thought of achiving this by two approach, one creating the USI but the problem is NUPI and USI are going to be same column and there will be overhead of creating subtable .

another way is to create primary key instead of creating USI. we will be inserting around 50 to 70 million records from stage to target once in a month and target table has around 1.5 billion records. whether it is adviceable to create primary key over USI for this scenario ?

Insert will be through direct select / insert instead of using teradata utilities.

Thanks
3 REPLIES
Junior Contributor

Re: USI vs Primary key to enforce uniqueness

Do you actually need the USI?
Do you access it in WHERE?

If it's only for the logical uniqueness, it's hopefully already checked in your ETL process:
Inserting 50 million rows with just a single row violating the USI will result in a Rollback.

Btw, a Primary Key is not another way, as it will result in a USI, too.

Dieter
Enthusiast

Re: USI vs Primary key to enforce uniqueness

Hi Dieter,

Thanks for the info.

I tested few scenarios... Created with non unique primary index on composite columns and table is partitioned on date column .. inserted 2 duplicate records to that table which has already billion of records and triggered USI creation script. but USI creation failed with error message *** Failure 2930 Last session request caused the DBS to crash.
Statement# 1, Info =0 , but not sure why it failed with that error message, i was expecting another error message secondary index failed due to duplicate... Kindly help to clarify

Thanks
Junior Contributor

Re: USI vs Primary key to enforce uniqueness

Oops, you're right, you better don't resubmit that query.
If you didn't do it already you should open an incident, this is a severe problem.

Dieter