MINVALUE

In Oracle, the MINVALUE option is used when creating or altering a sequence to specify the minimum value that the sequence can generate. This is an important part of sequence management, as it helps control the range of values a sequence can generate.

Here’s a detailed explanation of the MINVALUE option in Oracle sequences:

What is MINVALUE?

• The MINVALUE specifies the smallest value that a sequence can generate. It helps define the lower bound of the sequence, ensuring that the sequence does not generate values smaller than this specified value.
• It is used during the creation of a sequence or when altering an existing sequence to set a lower limit for the sequence.

Syntax for MINVALUE

CREATE SEQUENCE sequence_name

  START WITH start_value

  INCREMENT BY increment_value

  MINVALUE minimum_value;

For example:

CREATE SEQUENCE emp_seq

  START WITH 1000

  INCREMENT BY 1

  MINVALUE 1000;

In this example:

• The sequence starts at 1000.
• The smallest possible value the sequence can generate is 1000.

Default Behavior of MINVALUE

• By default, when you create a sequence, the MINVALUE is set to 1 for ascending sequences and -1 for descending sequences.
• For ascending sequences: MINVALUE defaults to 1.
• For descending sequences: MINVALUE defaults to -1.

Use of MINVALUE in a Sequence

The MINVALUE option is useful when you need to restrict the sequence's value to a specific lower boundary. This is especially important for scenarios where you want to ensure that the sequence value doesn't fall below a certain threshold, which may be required for business rules, data integrity, or specific requirements.

Example of MINVALUE in Ascending Sequence:

CREATE SEQUENCE emp_seq

  START WITH 1000

  INCREMENT BY 1

  MINVALUE 1000;

• Start Value: The sequence starts at 1000.
• Minimum Value: The smallest value the sequence can generate is 1000. It will never go below 1000.

Example of MINVALUE in Descending Sequence:

CREATE SEQUENCE emp_seq

  START WITH -1

  INCREMENT BY -1

  MINVALUE -10;

• Start Value: The sequence starts at -1 and decreases by 1.
• Minimum Value: The smallest value the sequence can generate is -10. The sequence will not go below -10.

Key Points to Remember About MINVALUE

1. Cannot go below MINVALUE:
• The sequence cannot generate values smaller than the MINVALUE. If the sequence reaches the MINVALUE and the next value would fall below it, Oracle will raise an error (unless the sequence is set to cycle).
2. Setting MINVALUE and MAXVALUE Together:
• In some cases, you may want to set both a MINVALUE and a MAXVALUE to control the upper and lower bounds of the sequence. This is especially useful when defining the range of values for a sequence in certain applications.

Example:

CREATE SEQUENCE emp_seq

  START WITH 1

  INCREMENT BY 1

  MINVALUE 1

  MAXVALUE 1000;

3. Use of CYCLE with MINVALUE:
• When a sequence is created with the CYCLE option, and it reaches the MINVALUE, it will wrap around and generate values again. Without CYCLE, the sequence will throw an error when it reaches the MINVALUE.

Example with CYCLE:

CREATE SEQUENCE emp_seq

  START WITH 1

  INCREMENT BY 1

  MINVALUE 1

  CYCLE;

• After reaching the MINVALUE (1 in this case), the sequence will loop back to MAXVALUE or restart at the START WITH value if it has already exceeded the MAXVALUE.
3. MINVALUE in Non-Cycling Sequences:
• If you define a sequence without the CYCLE option, once the sequence hits the MINVALUE, it will stop and throw an error when you attempt to call NEXTVAL again.
4. Effect on Sequence Behavior:
• For ascending sequences: MINVALUE affects the smallest value that can be generated. If no MINVALUE is defined, it defaults to 1.
• For descending sequences: MINVALUE affects the largest negative value that can be generated. If no MINVALUE is defined, it defaults to -1.
5. MINVALUE vs. START WITH:
• The START WITH value may be higher or lower than the MINVALUE. The sequence will start at the START WITH value and continue according to the increment (INCREMENT BY) until it reaches the MINVALUE (for ascending sequences) or the MAXVALUE (for descending sequences).
• If START WITH is greater than MINVALUE in an ascending sequence, the sequence will first generate values higher than MINVALUE and then approach the lower bound.

Example: Sequence With MINVALUE and CYCLE

CREATE SEQUENCE emp_seq

  START WITH 100

  INCREMENT BY 10

  MINVALUE 10

  MAXVALUE 100

  CYCLE;

• Behavior: This sequence will start at 100 and increment by 10. Once the sequence reaches 10 (the MINVALUE), it will wrap around to the MAXVALUE (100), and continue incrementing, creating a cycle between 100 and 10.

 

How to Alter MINVALUE for an Existing Sequence

You cannot directly modify the MINVALUE of an existing sequence using the ALTER SEQUENCE statement. Instead, you would need to drop and recreate the sequence with the new MINVALUE.

Example:

DROP SEQUENCE emp_seq;

 

CREATE SEQUENCE emp_seq

  START WITH 1000

  INCREMENT BY 1

  MINVALUE 500;

Practical Use Cases for MINVALUE

1. Preventing Negative Values: If you have a sequence for generating employee IDs or invoice numbers, you might want to ensure the sequence starts at a positive value and never generates negative numbers.
2. Controlling Number Range: In financial or inventory systems, you may want a sequence to generate values within a specific range, e.g., invoice numbers that cannot be smaller than a certain value.
3. Ensuring Compliance with Business Rules: Some business rules might require that a sequence of numbers adhere to specific ranges (e.g., order IDs starting from 1000).

 

Conclusion

• MINVALUE allows you to set a lower bound for the sequence, ensuring that it does not generate values smaller than a specified threshold.
• It is particularly useful when you need to manage the range of generated sequence values, either to enforce business rules or maintain data integrity.
• It can be used with both ascending and descending sequences and can be combined with MAXVALUE and CYCLE to create more complex sequence behaviors.