SET and MULTISET
SET tables obey set theory semantics and the relational data model by not permitting duplicate rows in a table.
MULTISET tables obey bag, or multiset, semantics. They violate set theory semantics and the relational data model by permitting duplicate rows in a table.
|
IF you specify this keyword … |
THEN duplicate rows are … |
|
SET |
not permitted for the table. |
|
MULTISET |
permitted for the table in compliance with the ANSI SQL:2011 standard. |
If neither SET nor MULTISET is specified explicitly, then the table kind assignment is made according to the rules stated in the following table.
|
IF the session creating the table is in this mode … |
THEN this keyword is implied … |
|
ANSI |
MULTISET |
|
Teradata |
SET |
There are 2 exceptions to this rule.
In this case, the default table kind is the table kind of the source table irrespective of the current session mode.
In this case, the default table kind is always MULTISET, regardless of the session mode or the setting of the DBS Control parameter PrimaryIndexDefault.
Teradata Database creates unpartitioned NoPI (see “Unpartitioned NoPI Tables” on page 575) and column‑partitioned tables (see “Column‑Partitioned Tables” on page 577) as MULTISET tables by default. Neither type can be created as, or altered to become, SET tables.
Some client utilities have restrictions with respect to MULTISET tables. For specific information on these restrictions, see the appropriate client utility manual.