Nullable Columns Are Valid for Unique Indexes - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
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.