SET collection types

A SET is an unordered collection of elements in which each element is unique. You define a column as a SET collection type when you want to store collections whose elements have the following characteristics:

The elements contain no duplicate values.
The elements have no specific order associated with them.

To illustrate how you might use a SET, imagine that your human resources department requires information about the dependents of each employee in the company. You can use a collection type to define a column in an employee table that stores the names of an employee's dependents. The following statement creates a table in which the dependents column is defined as a SET:

CREATE TABLE employee
(
   name        CHAR(30),
   address     CHAR (40),
   salary      INTEGER,
   dependents  SET(VARCHAR(30) NOT NULL)
);

A query against the dependents column for any given row returns the names of all the dependents of the employee. In this case, SET is the correct collection type because the collection of dependents for each employee should not contain any duplicate values. A column that is defined as a SET ensures that each element in a collection is unique.

To illustrate how to define a collection type whose elements are a row type, suppose that you want the dependents column to include the name and birthdate of an employee's dependents. In the following example, the dependents column is defined as a SET whose element type is a row type:

CREATE TABLE employee
(
   name        CHAR(30),
   address     CHAR (40),
   salary      INTEGER,
   dependents  SET(ROW(name VARCHAR(30), bdate DATE) NOT NULL)
);

Each element of a collection from the dependents column contains values for the name and bdate. Each row of the employee table contains information about the employee and a collection with the names and birthdates of the employee's dependents. For example, if an employee has no dependents, the collection for the dependents column is empty. If an employee has 10 dependents, the collection should contain 10 elements.