Chapter 12 – Reference types¶

REF¶

The required syntax for a <reference type> specification is as follows.

<reference type> ::=
REF (<UDT name>)
  [ SCOPE <Table name> [reference scope check] ]
   <reference scope check> ::=
   REFERENCES ARE [NOT] CHECKED
   [ ON DELETE
      {CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION} ]

A <reference type> specification defines a pointer: its value is a value that references some site. (A site either does or does not have a REF value.) For example, this REF specification defines a <reference type> based on a UDT (the “referenced type”) called my_udt:

REF(my_udt)

As already mentioned, a REF is a pointer. The value in a REF coloumn “refers” to a row in a Base table that has the with REF value property (this is a known as a typed table). The row that the REF value points to contains a value of the UDT that the REF Column is based on.

If you’re putting a REF specification in an SQL-Schema statement, the <AuthorizationID> that owns the containing Schema must have the USAGE Privilege on “<UDT name>”. If you’re putting a REF specification in any other SQL statement, then your current <AuthorizationID> must have the USAGE Privilege on “<UDT name>”.

For each site that has a REF value and is defined to hold a value of the referenced UDT, there is exactly one REF value – at any time, it is distinct from the REF value of any other site in your SQL-environment. The <data type> of the REF value is REF (UDT).

[NON-PORTABLE] The data type and size of a REF value in an application program must be some number of octets but is non-standard because the SQL Standard requires implementors to define the octet-length of a REF value.

A REF value might have a scope: it determines the effect of a dereference operator on that value. A REF value’s scope is a list of Base table names and consists of every row in every one of those Base tables. The optional SCOPE clause of a <reference type> specification identifies REF’s scope. Each Table named in the SCOPE clause must be a referenceable Base table with a structured type that is the same as the structured type of the UDT that REF is based on. Here is an examples:

CREATE TABLE Table_1 (
   column_1 SMALLINT,
   column_2 REF(my_udt) SCOPE Table_2);

If you omit the SCOPE clause, the scope defaults to the Table that owns the Column you’re defining.

If your REF specification with a SCOPE clause is part of a <Field definition>, it must include this <reference scope check>: REFERENCES ARE [NOT] CHECKED ON DELETE NO ACTION. If a REF specification with a SCOPE clause is part of a <Column definition>, it must include a <reference scope check> with or without the optional ON DELETE sub-clause. The <reference scope check> clause may not be used under any other circumstances.

A <reference type> is a subtype of a <data type> if (a) both are <reference type>s and (b) the UDT referenced by the first <reference type> is a subtype of the UDT referenced by the second <reference type>.

If you want to restrict your code to Core SQL, don’t use the REF <data type>.

CAST¶

In SQL, CAST is a scalar operator that converts a given scalar value to a given scalar <data type>. CAST, howver, can´t be used with <reference type>s. To cast REF values, you´ll have to use a user-defined cast.

It isn’t, of course, possible to convert the values of every <data type> into the values of every other <data type>. You can cast a <reference type> source to a UDT target and to any SQL predefined <data type> target (except for <collection type>s and <row type>s) provided that a user-defined cast exist for this purpose and your current <AuthorizationID> has the EXECUTE Privilege on that user-defined cast. When you cast a <reference type> to any legal target, your DBMS incokes the user-defined cast. When you cast a <reference type> to any legal target, your DBMS invokes the user-defined cast routine´s argument. The cast result in the value returned by the user-defined cast.

Scalar functions¶

SQL provides two scalar functions that operate on or return a <reference type>: the <dereference operation> and the <reference resolution>.

<dereference operation>¶

The required syntax for a <dereference operation> is as follows.

<dereference operation> ::=
reference_argument -> <Attribute name>

The <dereference operation> operates on two operands — the first must evaluate to a <reference type> that has a non-empty scope and the second must be the name of an Attribute of the <reference type>’s UDT. The <dereference operation> allows you to access a Column of the row identified by a REF value; it returns a result whose <data type> is the <data type> of <Attribute name> and whose value is the value of the system-generated Column of the Table in the <reference type>’s scope (where the system-generated Column is equal to reference_argument). That is, given a REF value, the <dereference operation> returns the value at the site referenced by that REF value. If the REF value doesn’t identify a site (perhaps because the site it once identified has been destroyed), the <dereference operation> returns NULL.

If you want to restrict your code to Core SQL, don’t use the <dereference operation>.

<reference resolutions>¶

The required syntax for a <dereference operation> is as follows.

<dereference operation> ::=
reference_argument -> <Attribute name>

DEREF operates on any expression that evaluates to a <reference type> that has a non-empty scope. It returns the value referenced by a REF value. Your current <AuthorizationID> must have the SELECT WITH HIERARCHY Privilege on reference_argument’s scope Table.

If you want to restrict your code to Core SQL, don’t use DEREF.

Set Functions¶

SQL provides three set functions that operate on a <reference type>: COUNT and GROUPING. Since none of these operate exclusively with REF arguments, we won’t discuss them here; look for them in our chapter on set functions.

Predicates¶

In addition to the comparison operators, SQL provides eight other predicates that operate on <reference type>s: the <between predicate>, the <in predicate>, the <null predicate>, the <exists predicate>, the <unique predicate>, the <match predicate>, the <quantified predicate> and the <distinct predicate>. Each will return a boolean value: either TRUE, FALSE or UNKNOWN. Since none of them operates strictly on <reference type>s, we won’t discuss them here. Look for them in our chapters on search conditions.