Nullable Columns Are Valid for Unique Indexes - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

While columns defined with PRIMARY KEY or UNIQUE constraints not only cannot be null, but must also be explicitly defined as NOT NULL, nullable columns are valid for UPI and NUPI column definitions. This is not good database design practice, and you should only permit nullable columns in index definitions under extraordinary conditions.

Note that these semantics demand that nulls in unique index columns must be treated as if they are equal to one another even though SQL nulls only represent an absence of value, which means they can neither be equated nor compared in any other way. Also note that all null primary index rows hash to the same AMP, so even a UPI can produce row distribution skew if you allow it to be null. For a description of other inconsistencies in the semantics of SQL nulls, see Teradata Vantage™ - Database Design, B035-1094.

IF an index declared as UNIQUE or UNIQUE PRIMARY consists of … THEN …
a single column only 1 row can exist in the table with a null for that column.
multiple columns the table cannot have more than 1 row in which all the values are identical because duplicate rows are not permitted when uniqueness is defined.

See Teradata Vantage™ - Database Design, B035-1094 for additional information about nulls in database design, their potential for causing problems in various situations, and suggestions about how to avoid those problems.