# Structured type

> Mediated Wiki article. Canonical URL: https://mediated.wiki/source/Structured_type
> Markdown URL: https://mediated.wiki/source/Structured_type.md
> Source: https://en.wikipedia.org/wiki/Structured_type
> Source revision: 1323278925
> License: Creative Commons Attribution-ShareAlike 4.0 International (https://creativecommons.org/licenses/by-sa/4.0/)

Custom SQL data type

The [SQL:1999](/source/SQL%3A1999) standard introduced a number of [object–relational database](/source/Object%E2%80%93relational_database) features into [SQL](/source/SQL), chiefly among them **structured user-defined types**, usually called just **structured types**. These can be defined either in plain SQL with CREATE TYPE but also in Java via [SQL/JRT](/source/SQL%2FJRT). SQL structured types allow [single inheritance](/source/Single_inheritance).

Structured types are supported to varying degrees in [Oracle Database](/source/Oracle_Database), [IBM Db2](/source/IBM_Db2), [PostgreSQL](/source/PostgreSQL) and [Microsoft SQL Server](/source/Microsoft_SQL_Server), although the latter only allows structured types defined in [CLR](/source/Common_Language_Runtime).

## SQL examples

### Object structured type

In order to define a custom structure type using [Oracle Database](/source/Oracle_Database) one could use statements such as these:

CREATE TYPE Person_Type AS OBJECT (
    person_title VARCHAR2(10),
    person_first_name VARCHAR2(20),
    person_last_name VARCHAR2(20),
)
NOT FINAL;

Such structure type can be then used to create a table that would also hold all columns defined in *Person_Type*:

CREATE TABLE Person_Table OF Person_Type;

Custom structure types support inheritance, which means that one can create another type that inherits from previous. NOT FINAL statement must be however included in a base structure type definition in order to allow for creation of any other subtypes.

CREATE TYPE Student_Type UNDER Person_Type (
    matriculation_number NUMBER(10)
);

*Student_Type* then could be used in order to create a *Student_Table* which will include all columns defined in *Person_Type* as well. [Primary Key](/source/Unique_key) and [Constraints](/source/Database_constraint) should be defined during or after creation of table and cannot be defined inside structure type itself.

CREATE TABLE Student_Table OF Student_Type (
    matriculation_number PRIMARY KEY,
    CONSTRAINT person_title_not_null_constraint NOT NULL (person_title),
);

Each custom structure type can also contain other types in order to support more complex structures:

CREATE TYPE Address_Type AS OBJECT (
    address_street VARCHAR2(30),
    address_city VARCHAR2(30),
);

CREATE TYPE University AS OBJECT (
    university_name VARCHAR2(30),
    university_address Address_Type
);

## Further reading

- Jim Melton (2003). *Advanced SQL: 1999*. Morgan Kaufmann. [ISBN](/source/ISBN_(identifier)) [978-1-55860-677-7](https://en.wikipedia.org/wiki/Special:BookSources/978-1-55860-677-7). Chapters 2-4.

- Suzanne W. Dietrich; Susan D. Urban (2011). *Fundamentals of Object Databases: Object-Oriented and Object-Relational Design*. Morgan & Claypool Publishers. [ISBN](/source/ISBN_(identifier)) [978-1-60845-476-1](https://en.wikipedia.org/wiki/Special:BookSources/978-1-60845-476-1). Chapter 3.

- Catherine Ricardo (2011). [*Databases Illuminated*](https://archive.org/details/databasesillumin0000rica_2ed) (2nd ed.). Jones & Bartlett Publishers. [ISBN](/source/ISBN_(identifier)) [978-1-4496-0600-8](https://en.wikipedia.org/wiki/Special:BookSources/978-1-4496-0600-8). Chapter 8.

This database-related article is a stub. You can help Wikipedia by adding missing information.

- [v](https://en.wikipedia.org/wiki/Template:Database-stub)
- [t](/source/Template_talk%3ADatabase-stub)
- [e](https://en.wikipedia.org/wiki/Special:EditPage/Template:Database-stub)

---
Adapted from the Wikipedia article [Structured type](https://en.wikipedia.org/wiki/Structured_type) by Wikipedia contributors ([contributor history](https://en.wikipedia.org/wiki/Structured_type?action=history)). Available under [Creative Commons Attribution-ShareAlike 4.0 International](https://creativecommons.org/licenses/by-sa/4.0/). Changes may have been made.