This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.

The CREATE TYPE statement defines a UDT. The required syntax for the CREATE TYPE statement is as follows.

CREATE TYPE <UDT name>
[ UNDER <supertype UDT name> ]
[ AS {<predefined type> | <Attribute definition list>} ]
[ {INSTANTIABLE | NOT INSTANTIABLE} ]
{FINAL | NOT FINAL}
[ <reference type specification> ]
[ <cast option> ]
[ <method signature> [ {,<method signature> }... ] ]

   <Attribute definition list> ::=
   (<Attribute definition> [ {,<Attribute definition>}... ])

      <Attribute definition> ::=
      <Attribute name> { <data type> | <Domain name> }
      [ REFERENCES ARE [ NOT ] CHECKED [ ON DELETE
          {NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT} ] ]
      [ DEFAULT default value ]
      [ COLLATE <Collation name> ]

   <reference type specification> ::=
   REF USING <predefined type> [ <ref cast option> ] |
   REF <Attribute name> [ {,<Attribute name>} ... ] |
   REF IS SYSTEM GENERATED

      <ref cast option> ::=
      [ CAST (SOURCE AS REF) WITH <cast-to-ref identifier> ]
      [ CAST (REF AS SOURCE) WITH <cast-to-type identifier> ]

   <cast option> ::=
   [ CAST (SOURCE AS DISTINCT)WITH <cast to distinct identifier> ]
   [ CAST (DISTINCT AS SOURCE) WITH <cast to source identifier> ]

   <method specification> ::=
   <original method specification> |
   OVERRIDING [ INSTANCE | STATIC ] <partial method specification>

      <original method specification> ::=
      [ INSTANCE | STATIC ] <partial method specification>
      [ SELF AS RESULT ] [ SELF AS LOCATOR ]
      [ LANGUAGE {ADA | C | COBOL | FORTRAN | MUMPS | PASCAL | PLI | SQL} ]
      [ PARAMETER STYLE {SQL | GENERAL} ]
      [ [ NOT ] DETERMINISTIC ]
      [ {NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA} ]
      [ {RETURN NULL ON NULL INPUT | CALL ON NULL INPUT} ]

      <partial method specification> ::=
      METHOD <routine name>
      (SQL parameter declaration list)
      RETURNS <data type>

CREATE TYPE defines a new UDT: a named set of valid data values. A UDT is owned by the Schema it belongs to.

  • The <UDT name> identifies the UDT and the Schema that it belongs to. A <UDT name> that includes an explicit <Schema name> qualifier belongs to the Schema named. A <UDT name> that does not include an explicit <Schema name> qualifier belongs to the SQL-session default Schema. The <UDT name> must be unique (for all Domains and UDTs) within the Schema that owns it. If CREATE TYPE is part of a CREATE SCHEMA statement, the <UDT name>, if explicitly qualified, must include the <Schema name> of the Schema being created; that is, it isn't possible to create a UDT belonging to a different Schema from within CREATE SCHEMA.

There are actually three variants of CREATE TYPE, used in distinct ways:

Making structured types (garden variety UDTs)

##CREATE TYPE## <name>
AS <Attribute definitions>
...
NOT FINAL

Making structured types (subtype UDTs)

##CREATE TYPE## <name>
UNDER <name>
...
[NOT] FINAL

Making distinct types (not really truly UDTs)

##CREATE TYPE## <name>
AS <predefined type>
...
FINAL

If the representation clause is "AS <predefined data type>" for example:

CREATE TYPE UDT_1 AS CHAR(15) FINAL

then this UDT is a distinct type. Usually when we talk about UDTs we mean the other kind of UDT the structured type because there's not much that one can do with a distinct type.

If you use a subtype clause for example:

CREATE TYPE a UNDER b ...

you are making a new subtype under an existing supertype. The supertype must exist, it must be a structured type UDT that was defined as NOT FINAL and you must have the UNDER Privilege on it. Notice that there can be only one supertype; this means that SQL, like Java, has a "single inheritance" rule. Notice too that you can have both a subtype clause and an Attribute definition list in the same CREATE TYPE statement; this means that the subtype can have both inherited Attributes (Attributes taken from the supertype) and original Attributes (Attributes taken from the new definition, which follow the inherited Attributes).

If the representation is "... AS <Attribute list> ...", then each Attribute definition must look very much like a <Column definition> looks in a CREATE TABLE statement for example:

attribute_1 CHAR(1000) CHARACTER SET ISO8BIT,
attribute_2 TIME WITH TIME ZONE DEFAULT '12:00:00'

Constraints (such as NOT NULL) are illegal, though. An <Attribute name> is an <identifier>; all <Attribute name>s must be unique within their UDT. The Attribute's <data type> may be a UDT, but cyclic references are illegal.

The Standard is contradictory about the instantiable clause. It's safe to assume that "instantiable", as in all OO languages, means "can be instantiated" (a "non-instantiable" or "abstract" UDT would have no instances but could have instantiable subtypes). Use of typed Tables is only possible if the type is intantiable.

In the finality clause, FINAL means (as in Java) that the new UDT may have no proper subtypes. That is, no further UDT may be created UNDER it. (There is a belief that the clause is for distinguishing distinct types from structured types. We don't believe that. We repeat that a distinct type s a UDT defined with "AS <predefined type>", all other UDTs are structured types.) If the CREATE TYPE statement contains any Attribute definitions, then NOT FINAL is mandatory. Otherwise either FINAL or NOT FINAL is mandatory.

The reference specification is either "user-generated" (REF USING), "derived" (REF <Attribute list>) or "system-generated" (REF IS SYSTEM GENERATED). With the default REF IS SYSTEM GENERATED there is no further specification because values are implementation-dependent. With the main option REF USING there is a further specification: the <Attribute name> list. Because (as in pure relational systems) a row's uniqueness should depend on Column values, the <Attribute name>s here would be an indirect list of a "key value".

The cast option is legal only for distinct types. The cast's source and target <data type>s are the <predefined type> specified earlier in the CREATE TYPE statement and the name of the UDT, respectively.

A UDT's methods are defined in pretty well the same way as functions; indeed a method is a function, albeit a function which cannot live without its UDT and which is called in a slightly different way than a regular function. The default method characteristics are LANGUAGE SQL, PARAMETER STYLE SQL, NOT DETERMINISTIC, CONTAINS SQL, RETURN NULL ON NULL INPUT. (Note: Although the Standard says that NOT DETERMINISTIC is the default, it's hard to believe that this is the true intent.) The difference between an "overriding" and an "original" method is that an overriding method "overrides" an already-existing method with the same name, in some supertype. Method signatures must be distinct. Remember that, as noted in the tutorial section of this chapter, structured UDTs have implicitly-created methods: one constructor method for the UDT as a whole, n mutator methods (one for each Attribute), and n observer methods (one for each Attribute). As for distinct types: apparently no implicitly-created methods exist for them, but there may be a "transform" function for casting to/from host languages.

Distinct types

The simplest way to make a UDT is with:

CREATE TYPE <UDT name> AS <predefined data type> FINAL;

which since it has no Attribute list is not a specification of a "structured type" with all the OO trimmings. Instead, UDTs made this way are called "distinct types". The main idea behind distinct types is that they constitute enforceable domains. For example, suppose we define two currency data types:

CREATE TYPE euro AS DECIMAL(8,2) FINAL;

CREATE TYPE mark AS DECIMAL(8,2) FINAL;

If we now attempt to pass a "euro" value to a "mark" target, we will fail the distinct type provides us with a simple form of type checking that we cannot achieve using an SQL Domain.

Distinct types have methods, just like structured types. However, they are limited in various ways. Usually, when we make generalized comments about object-orientation analogies such as "UDTs are classes" we have structured types in mind, not distinct types.

Comments

Comments loading...