Creating a sequence for a column

Chapter 3 Building Physical Diagrams

Creating a sequence for a column

When your DBMS supports the feature, you can create a sequence for a column.

To understand sequences, you must first understand auto-incremented columns. An auto-incremented column is one whose values automatically increment by 1 thereby producing a list of consecutive numbers. Sequences are special auto-incremented columns with features that allow you to define a more complex list of numbers. For example, sequences allow you to define a list of numbers ranging between two values with an increment by any number (integer) you want.

Once you define a sequence, you can apply and enable it to a column. The data type for the column receiving the sequence must be a numeric data type. Such auto-incremented columns can be used in a key for a PDM table.

For more information on data types, see section Selecting a data type for a column.

Using sequences

There are two steps to using sequences:

Create a sequence
Apply and enable a sequence to a column

When you create a sequence, you have to define options in sequence properties. It is also possible to assign the options to other existing sequences.

Once a sequence is created, you apply it to a table column in the model. You then rebuild triggers to enable the sequence on this column.

For more information on model checks, see the "Checking a PDM" section in the Working with PDMs chapter.

Effect of DBMS change on sequences and auto-incremented columns

If you create a sequence attached to a column in a DBMS supporting sequences, such as Oracle 8 and higher, Interbase or PostgreSQL, or create an auto-incremented column in a DBMS supporting this feature, and then decide to change the target DBMS, the following effects occur:

DBMS change	Defined in original DBMS	Effect on sequence objects and auto-incremented columns
DBMS supporting sequences to a DBMS supporting auto-incremented columns	Sequence attached to a column	The sequence disappears and the column to which it was attached becomes an auto-incremented column in the DBMS
DBMS supporting auto-incremented columns to a DBMS supporting sequences	Auto-incremented column	The auto-incremented column is deleted and replaced by a sequence object called S_TABLENAME which is attached to the original column

Sequences and intermodel generation

When a CDM or an OOM is generated from a PDM, the data type of a table column attached to a sequence is translated to a numeric data type in the new model:

PDM generated to	Sequence is converted to
CDM	A serial data type for an entity property. The data type has the format `NO%n` where `%n` is a number indicating the length of the data type
OOM	A serial data type for a class attribute. The data type has the format `NO%n` , where `%n` is a number indicating the length of the data type

Example

Assume that you want to create a column listing the months of the year when quarterly reports are published: March, June, September, and December. The first report is published on the third month, the second on the sixth, the third on the ninth and the last on the twelfth.

You can define the proper sequence by typing the following values for sequence option parameters:

Parameter name	Description	Value
`Start with`	March is the third month of the year	3
`Increment by`	Look three months ahead to identify the next month in the list	3
`Maxvalue`	Stop when you have reached the last month of the year	12

The sequence created with these parameter settings allows you to automatically create the list of months in a year when quarterly reports are published.