How to increase sequence by condition in Oracle Database 19.x?

Question

We have the table as below.

CREATE TABLE "STYLES" (
    "STYLE_CODE" CHAR(8) GENERATED ALWAYS AS
        ("COLOR"||"FEATURE"||"SIZE"||"YEAR") 
        VIRTUAL NOT NULL ENABLE, 
    "COLOR" CHAR(2), 
    "FEATURE" CHAR(2), 
    "SIZE" CHAR(2), 
    "YEAR" CHAR(2),
    "SEQ" CHAR(2), 
    CONSTRAINT "STYLE_CODE_PK" PRIMARY KEY 
        ("STYLE_CODE") USING INDEX ENABLE
    ) ;

If the value of STYLE_CODE PK is not duplicated in the table above, the SEQ value is always generated as 00

If the value of STYLE_CODE PK is duplicated, we want the SEQ value to be increased in the order of 01, 02.

I want to find a solution. Thank you.

Answer 1

Sequence objects don't work that way: they are meant to produce unique values. Those values may have gaps or (in some cases, such as with Oracle RAC) even be out of order. You'd need a separate sequence for every possible STYLE_CODE, and/or complicated business logic with manual row locking that won't scale well - what if two users add new rows with the same STYLE_CODE at the same time? Recommend a separate PK column that is just a sequence-generated number, with STYLE_CODE as an attribute, and (in your case) a single column for the value based on datatype rather than different for each. Also, your STYLE_CODE virtual column expression doesn't appear to be valid; it should be a CASE statement, calculation or something that returns a derived value, not just a list of acceptable values.

CREATE TABLE STYLES (
    STYLE_ID NUMBER GENERATED BY DEFAULT AS IDENTITY(START with 1 INCREMENT by 1),
    STYLE_CODE VARCHAR2(8) NOT NULL, 
    STYLE_VALUE_CHAR VARCHAR2(2), 
    STYLE_VALUE_NUM NUMBER,
    CONSTRAINT STYLE_PK PRIMARY KEY 
        (STYLE_ID) USING INDEX ENABLE,
    CONSTRAINT check_style_code
        CHECK (STYLE_CODE IN ('COLOR','FEATURE',
        'SIZE','YEAR')) ENABLE
    ) ;

If you must have a composite style field for reporting purposes, consider the approach described here: https://stackoverflow.com/questions/67914853/how-to-set-an-autoincrement-composite-primary-key-in-oracle19c and see if it can be adapted to your purpose. It doesn't store the field-dependent counter in the database, but generates it as part of the query.

Alternatively, consider keeping each type in a separate table, each with its own counter and appropriate attributes.

A couple other words of advice:

• Don't use double quotes in Oracle DDL that you plan to actually execute. While many tools generate code that way by default, it's considered bad practice in Oracle because it forces ALL SQL that uses those objects to also use double quotes for case sensitivity, when Oracle is natively case-insensitive.
• Never store numbers, like YEAR or SEQ as characters; always store them as numbers (also, always use a 4-digit year).
• Use VARCHAR2 instead of CHAR datatype. Your DBA will thank you for not wasting storage space on the server.