The SQL:1999 standard introduced a number of

object–relational database An object–relational database (ORD), or object–relational database management system (ORDBMS), is a database management system (DBMS) similar to a relational database, but with an object-oriented database model: objects, classes and inheritan ...

features into 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 SQL/JRT, or ''SQL Routines and Types for the Java Programming Language'', is an extension to the SQL standard first published as ISO/IEC 9075-13:2002 (part 13 of SQL:1999). SQL/JRT specifies the ability to invoke static Java methods as routines fr ...

. SQL structured types allow

single inheritance Multiple inheritance is a feature of some object-oriented computer programming languages in which an object or class can inherit features from more than one parent object or parent class. It is distinct from single inheritance, where an object or ...

. Structured types are supported to varying degrees in

Oracle Database Oracle Database (commonly referred to as Oracle DBMS, Oracle Autonomous Database, or simply as Oracle) is a multi-model database management system produced and marketed by Oracle Corporation. It is a database commonly used for running online t ...

IBM Db2 Db2 is a family of data management products, including database servers, developed by IBM. It initially supported the relational model, but was extended to support object–relational features and non-relational structures like JSON a ...

PostgreSQL PostgreSQL (, ), also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the In ...

and

Microsoft SQL Server Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which ma ...

, although the latter only allows structured types defined in

CLR CLR may refer to: * Calcium Lime Rust, a household cleaning-product * California Law Review, a publication by the UC Berkeley School of Law * Tube_bending, Centerline Radius, a term in the tubing industry used to describe the radius of a bend * Cen ...

SQL examples

Object structured type

In order to define a custom structure type using

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 In the relational model of databases, a primary key is a ''specific choice'' of a ''minimal'' set of attributes (Column (database), columns) that uniquely specify a tuple (Row (database), row) in a Relation (database), relation (Table (database), t ...

and Constraints 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 );

SQL examples

Object structured type

Further reading