SQL in a Nutshell, 2nd Edition

Name

CREATE/ALTER TYPE Statement

Synopsis

The CREATE TYPE statement allows you to create a user-defined type (UDT); that is, a user-defined datatype or “class” in object-oriented terms. UDTs extend SQL capabilities into the realm of object-oriented programming by allowing inheritance and other object-oriented features. You can also create something called typed tables with the CREATE TABLE statement using a previously created type made with the CREATE TYPE statement. Typed tables are based on UDTs and are equivalent to “instantiated classes” from object-oriented programming.

Platform	Command
DB2	Supported, with variations
MySQL	Not supported
Oracle	Supported, with limitations
PostgreSQL	Supported, with variations
SQL Server	Not supported

SQL2003 Syntax

CREATE TYPE type_name
[UNDER supertype_name]
[AS [new_udt_name] datatype [attribute] [,...]
   {[REFERENCES ARE [NOT] CHECKED
      [ON DELETE
         {NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT} ] ] |
   [DEFAULT value] |
   [COLLATE collation_name] } ]
   [ [NOT] INSTANTIABLE]
   [ [NOT] FINAL]
[REF IS SYTEM GENERATED |
   REF USING datatype
     [CAST {(SOURCE AS REF) | (REF AS SOURCE) WITH identifier} ] |
   REF new_udt_name [,...] } ]
[CAST {(SOURCE AS DISTINCT) | (DISTINCT AS SOURCE)} WITH identifier]
[method_definition [,...] ]

The syntax below alters an existing user-defined datatype:

ALTER TYPE type_name {ADD ATTRIBUTE type_definition |
   DROP ATTRIBUTE type_name}

Keywords

{CREATE | ALTER} TYPE type_name

Creates a new type or alters an existing ...