O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required