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,
CONSTRAINT STYLE_PK PRIMARY KEY
(STYLE_ID) USING INDEX ENABLE,
CHECK (STYLE_CODE IN ('COLOR','FEATURE',
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
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.