Add and drop casts on a distinct type

To enforce strong typing on a distinct type, the database server provides explicit casts to handle conversions between a distinct type and its source type. However, the creator of a distinct type can drop the existing explicit casts and create implicit casts, so that conversions between a distinct type and its source type do not require an explicit cast.

Important: When you drop the explicit casts between a distinct type and its source type that the database server provides, and instead create implicit casts to handle conversions between these data types, you diminish the distinctiveness of the distinct type.

The following DROP CAST statements drop the two explicit casts that were automatically defined on the movie_type:

DROP CAST(movie_type AS VARCHAR(30));
DROP CAST(VARCHAR(30) AS movie_type);

After the existing casts are dropped, you can create two implicit casts to handle conversions between movie_type and VARCHAR. The following CREATE CAST statements create two implicit casts:

CREATE IMPLICIT CAST (movie_type AS VARCHAR(30));
CREATE IMPLICIT CAST (VARCHAR(30) AS movie_type);

You cannot create a cast to convert between two data types if such a cast already exists in the database.

If you create implicit casts to convert between the distinct type and its source type, you can compare the two types without an explicit cast. In the following statement, the comparison between the video column and the laser_disc column requires a conversion. Because an implicit cast has been created, the conversion between VARCHAR and movie_type is implicit.

SELECT video FROM entertainment
   WHERE video = laser_disc