Object-Relational Databases:Reference Types
Reference Types
Object-oriented languages provide the ability to refer to objects. An attribute of a type can be a reference to an object of a specified type. For example, in SQL:1999 we can define a type Department with a field name and a field head which is a reference to the type Person, and a table departments of type Department, as follows:
Here, the reference is restricted to tuples of the table people. The restriction of the scope of a reference to tuples of a table is mandatory in SQL:1999, and it makes references behave like foreign keys.
We can omit the declaration scope people from the type declaration and instead make an addition to the create table statement:
In order to initialize a reference attribute, we need to get the identifier of the tuple that is to be referenced. We can get the identifier value of a tuple by means of a query. Thus, to create a tuple with the reference value, we may first create the tuple with a null reference and then set the reference separately:
This syntax for accessing the identifier of a tuple is based on the Oracle syntax. SQL:1999 adopts a different approach, one where the referenced table must have an attribute that stores the identifier of the tuple. We declare this attribute, called the self-referential attribute, by adding a ref is clause to the create table statement:
create table people of Person
ref is oid system generated
Here, oid is an attribute name, not a keyword. The subquery above would then use
select p.oid
instead of select ref(p).
An alternative to system-generated identifiers is to allow users to generate identifiers. The type of the self-referential attribute must be specified as part of the type definition of the referenced table, and the table definition must specify that the reference is user generated:
Comments
Post a Comment