Guidance relating to secondary index and unique column constraint

Database

Guidance relating to secondary index and unique column constraint

When we create a table and specify a unique constraint on a specific column(s), and we are not specifying any secondary indexes, but the table is created with secondary indexes with all the columns specified as unique.

This is actually a disk overhead as per my view. we might have a column as unique but doesn't have that column as a access path or to be straight we need not have it as a secondary index.

And also if we drop the secondary index the constraint unique is also dropped and viceversa.

Why secondary index is automatically getting created when we specify a column as unique.
1 REPLY
Teradata Employee

Re: Guidance relating to secondary index and unique column constraint

If you want the DBMS to enforce uniqueness, a Unique Index is the price you have to pay. This is clearly documented.

Without an index, enforcing the constraint "on the fly" during INSERT/UPDATE would be very costly (e.g. full table scans). By the way, this behavior is not unique to Teradata.