The SQL repository supports one-to-many relationships between two tables, and does not interpret the results according to any specific paradigm. This allows your application to apply whatever meaning you want to one-to-many relationships.

The SQL repository implements one-to-many relationships as multi-valued properties. To implement a multi-valued property, define the property with several <table> and <property> attributes:

<table> attributes

The <table> tag for a multi-valued property must set the following attributes:


Set to:



For example:

<table name="..." type="multi" ...


The appropriate table column name. For example:

<table name="..." type="multi" multi-column-name="idx" ...

The multi-column-name attribute ensures that the ordering of the multi-values are maintained. The column specified by the multi-column-name attribute is used for multi-valued properties of data type array, map, and list and is not used for sets (which are unordered). For map type properties, the values in the column specifiedy by the multi-column-name attribute must be a string. For list or array type properties, these values should be an integer or numeric type, and must be sequential.


The appropriate table column names.

As with auxiliary tables, the ordering of the ID column names is important. The columns specified by This attribute must list table columns in the same order as the id-column-names attribute of the primary table.

<property> attributes

The <property> tag for a multi-valued property sets the following attributes:


Set to:


One of the following:



For example:

<property ... data-type="array" ...


A primitive data type such as int and string, or the name of a user-defined property type (see User-Defined Property Types). For example:

<property name="interests" column-name="interest" 
  data-type="array" component-data-type="string"/>

Note that the SQL repository does not support references to binary types.


The item descriptor name of the referenced repository items. For example:

<property name="..." column-name="designers" 
   data-type="array" component-item-type="user"/>

Note: You cannot establish a default value for multi-valued properties.

The following example shows how the XML repository definition might specify the multi-valued property interests:

<item-descriptor name="user">
  <table name="dps_user" id-column-names="id" type="primary">
    <property name="login" data-type="string"/>
  <table name="dps_interest" type="multi" id-column-names="id"
    <property name="interests" column-name="interest" data-type="array"

See also the Sample SQL Repository Definition Files section in the SQL Repository Reference chapter for more examples of one-to-many relationships in repository definitions.

Allow null values

By default, null values are not allowed in multi-valued properties. You can specify to allow null values at two levels: