SQL Database Design » 8. User-Defined Types » 8.2 Distinct Types

8.2 Distinct Types

Distinct types were created in a need for type comparability. Domains allow all sorts of comparisons with other domains, whether that made sense or not. This rather lenient behavior led to unwanted comparisons and errors. This is why distinct types were created: to avoid comparability of otherwise compatible types. Distinct types add non-comparability to otherwise compatible types.

Distinct types were added in SQL:1999 (DB2 had them first). You create distinct types like:

CREATE DISTINCT TYPE customer_nbr AS INTEGER
CREATE DISTINCT TYPE order_nbr AS INTEGER

Any comparison against columns of these two types resulty in an error. The only way to perform a comparison, is by using the CAST function:

SELECT * FROM Orders WHERE CAST(customer_nbr AS INTEGER) = CAST(order_nbr AS INTEGER)

Last updated: 2010-08-04