In Derby DDL how to concatenate string and number as identity

Question

I've looked everywhere in the last 3 hours but still can't get this to work. I need to be able to concatenate the 1prefix and 1suffix value to form an id consisting of string and number.

How do I define the table.

create table dicounts_list
(
    DISCOUNT_PREFIX CHAR(20) NOT NULL WITH DEFAULT 'DISC',
    DISCOUNT_SUFFIX INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0001, INCREMENT BY 1),
    DISCOUNT_ID CHAR --concatenated values of DISCOUNT_PREFIX and DISCOUNT_SUFFIX e.g DISC0001
    DISCOUNT_NAME VARCHAR(30) NOT NULL,
    PRIMARY KEY(DISCOUNT_ID)
);

I need to be able to generate

DISC0001 DISC0002 DISC0003

on which only the numbers auto-increment every insertion to DiscounName. I just want to prefix the primary key with "DISC" as discount identifier.

I'm using Derby. I would really appreciate if you can help me with this or provide with alternative solution.

Thanks.

I would leave the `IDENTITY` column as it is and add the prefix in the data access/application logic, using a `NumberFormat` for padding with leading zeros. — Mick Mnemonic, Mar 06 '16 at 08:11
You want 'DISC' to be in its own column, but you **also** want it to be the first four characters of every primary key value? — Bryan Pendleton, Mar 06 '16 at 16:23

score 0 · Answer 1 · answered Mar 06 '16 at 10:16

A generated column should do the job. I don't have a Derby to test but following would work in DB2

create table t4 (prefix varchar(20) not null with default 'DISC',
                 suffix int generated always as identity (start with 1 increment by 1),
                 id generated always as (prefix || (substr2('000' || suffix, -4, 4))) )

score 0 · Answer 2 · answered Mar 06 '16 at 16:32

One alternative is to use a SEQUENCE, and the NEXT VALUE FOR syntax to generate new values for your primary keys, as documented here: http://db.apache.org/derby/docs/10.12/ref/rrefsqljnextvaluefor.html

For example, you could do:

create sequence discount_ids start with 1;
insert into discounts (id, name) 
    values ( 'DISC' || cast( next value for discount_ids as char(20)),
             'bubble gum' );

In Derby DDL how to concatenate string and number as identity

2 Answers2