SQL Database Design » 8. User-Defined Types » 8.4 Object-Relational Types

8.4 Object-Relational Types

The "new" standard SQL object-relational syntax allows you to create custom structured types. However, the object-relational extensions are no replacement for domains. They simply cannot deliver the same functionality as domains, that is, you cannot wrap an existing type with a check constraint and give that a name. For example, there is no way you can create a functionally equivalent boolean replacement.

Structured types have properties, much like classes. Every attribute can have a different type, even another structured one. The standard SQL object-relational syntax is as follows:

CREATE <value_or_object> TYPE <data-type>
[UNDER <super_type>]
[AS (<attribute_list>)]
[<method_declaration_list>]

<value_or_object> := VALUE | OBJECT

As an example, this defines an address type, which can be used elsewhere:

CREATE VALUE TYPE address AS
(
  street VARCHAR(50),
  addon  VARCHAR(50),
  zip    CHAR(5),
  city   VARCHAR(50)
)

The Oracle-specific syntax is very similar (the attribute declarations are exactly the same):

CREATE [OR REPLACE] TYPE address AS OBJECT
(
  ...
)

The keywords AS OBJECT are required when creating a structured type. There is no difference in using these types in standard SQL and Oracle:

CREATE TABLE Students
(
  id              INTEGER PRIMARY KEY,
  last_name       VARCHAR(30),
  first_name      VARCHAR(20),
  home_address    address,
  branch_of_study VARCHAR(30) NOT NULL
)

There is one problem with object-relational types: you can only create structured, object-like types. You cannot create domain-like types with the object-relational syntax. Thus, the object-relational types are not even a partial replacement for domains. My suspicion is, that the SQL standard folks have defined most elementary types any database architect ever needs with SQL:1999, so they probably did not see many uses for domains anymore. If every DBMS vendor had implemented the most important standard SQL type features, only a few people would complain about missing domains or domain-like functionality in DBMS products.

References for Object-Relational Types:

Last updated: 2010-08-04