SQL in a Nutshell, 3rd Edition

Name

CREATE/ALTER TYPE Statement

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
MySQL	Not supported
Oracle	Supported, with limitations
PostgreSQL	Supported, with variations
SQL Server	Supported, with variations

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 following syntax 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 type with the name type_name.

UNDER supertype_name

Creates ...