15.00 - Nullable Columns Are Valid for Unique Indexes - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Nullable Columns Are Valid for Unique Indexes

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 Database Design.

 

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 Database Design for additional information about nulls in database design, their potential for causing problems in various situations, and suggestions about how to avoid those problems.

Related Topics

Refer to the following Teradata Database Orange Book for a wealth of information about, and usage suggestions for, PPI and non-partitioned NoPI tables, respectively.

  • Jerry Klindt and Paul Sinclair, Partitioned Primary Index Usage (Single-Level and Multilevel Partitioning), Teradata Database Orange Book 541-0003869E02, 2008.
  • Tam Ly, No Primary Index (NoPI) Table User’s Guide, Teradata Database Orange Book 541‑0007565B02, 2009.
  • Refer to the following Teradata Database Orange Book for important usage information about column‑partitioned tables and join indexes.

  • Paul Sinclair, Increased Partition Limit and Other Partitioning Enhancements, Teradata Database Orange Book 541-0009027A02, 2011.
  • Paul Sinclair and Carrie Ballinger, Teradata Columnar, Teradata Database Orange Book 541-0009036A02, 2011.
  • See “CREATE TABLE” in SQL Data Definition Language Syntax and Examples for information about the syntax used to create row‑partitioned tables, non-partitioned NoPI tables, and column‑partitioned tables.

    See “ALTER TABLE (Basic Table Parameters)” on page 31 and “ALTER TABLE” in SQL Data Definition Language Syntax and Examples for information about how to modify table indexes.