Creating and Editing Sequences
Go Up to Creating and Editing Database Dependent Objects
Contents
Sequences are used to generate a series of integer values according to rules defined in the sequence. Database sequences are generally are used to create primary keys; they can also be used to generate random numbers.
The following database platforms support sequences:
- IBM DB2 for LUW
- Microsoft SQL Server
- Oracle
- Snowflake
Note: The Sequence Wizard and Sequence Editor share the same options, except for Attachment Bindings options which are present only in the editor.
Create or edit a Sequence
- In the Data Model Explorer, expand the Physical Main Model, right-click the Sequences node, and then select New Sequence.
- Complete the Sequences Wizard and then click Finish to create the sequence.
Tip: Once you have created the sequence, you can edit it by right-clicking the sequence you want to change, and then clicking Edit Sequence.
Name page/tab
- If you have already defined users for the selected database, you can click the list and choose a defined user for the sequence owner. Depending on the platform, you can also select a datatype for the sequence.
Numbers Page/Tab
Lets you specify
- The starting number for the sequence.
- The increment interval value for the sequence.
- The maximum value that can be generated for the sequence and the minimum value for the sequence.
Options page/tab
- Cycling: If you have specified a Maximum Value on the previous page/tab, you can choose to cycle the values.
- Ordering (DB2 and Oracle): Specify whether or not the sequence numbers must be generated in order of request. SQL Server 2012 does not support ordering
- Cache: Consider the following performance and application requirements trade-offs when choosing to enable the cache and the optimal cache size:
- Caching sequence numbers enables a range of sequence numbers to be kept in memory for fast access. If the next sequence number can be allocated from cache, number allocation can happen quickly; however, if the next sequence number cannot be allocated from cache, the application may have to wait for I/O operations to complete.
- Not caching sequence values ensures that there is no loss of values in the event of a system failure, shutdown or database deactivation. The values of the sequence are not stored in the cache and every sequence request results in synchronous I/O to the log.
Definition page/tab
Enter or edit a definition for the sequence. If the target database supports it, ER/Studio Data Architect adds this definition as a comment when generating SQL code.
DDL page/tab
Displays the CREATE SEQUENCE statement needed to build the sequence. ER/Studio Data Architect uses the platform-specific parser of the model's selected database platform to generate the sequence.
PreSQL & PostSQL page/tab
Lets you enter SQL to be applied before or after the CREATE SEQUENCE statement. If you select Generate, the PreSQL and PostSQL scripts entered here are included in the script when you generate the physical database.
Attachment Bindings page/ tab
Bind an external piece of information, or attachment to the sequence. You can also remove an attachment from an object, override an attachment binding's default value, or change the position of a bound attachment. To override the value of the attachment you have moved to the Selected Attachments grid, double-click the Value field of the target attachment. ER/Studio Data Architect opens the Value Override Editor or a list depending on the attachment datatype. Attachments are created in the Attachments folder of the Data Dictionary. For more information, see Attaching External Documents to the Data Model.
Permissions page/tab
Sets access roles and user permissions for the sequence. Keep in mind that the more specific permissions are, the more time you may have to spend maintaining and updating them. The Roles and Users must be previously assigned to appear in the Sequence Editor. For more information, see Granting and Revoking Permissions to Modify Database Objects.