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.