[Solved] Additional auto increment on no pk

[gkick]

Hi,

Is it possible to have a second auto increment field not being a pk like for instance I want a product code field (formatted PC-0012) in a tblProducts with pid being pk. If multiple auto increments are a no go is there another way to increment the fields value on a new rexcord?

Thanks

[F3K Total]

Hello,
one incrementation per table is enough to make records unique.
if both, pid and product code field (formatted PC-0012) depend on each other, why don't you create product code field by using a query?
I.e.

Code: Select all

SELECT "PID", 'PC-' || REPEAT( '0', 4 - LENGTH( "PID" ) ) || "PID" "PRODUCT" FROM "Table1"

[gkick]

@F3K Total,

Thats just brilliant, a simple concatination !!! Thanks a lot, now I have to find a way to put it on a label report.
Isn't fascinating that one always looks for complex solution
You ve made my day

GK

[F3K Total]

As i see, you use a HSQL 2.5.0 DB, using this,another possibility is to use a generated column, execute once via Tools/SQL... the following command:

Code: Select all

CREATE TABLE "TBL_AUTO_PRODUCTCODE" ("PID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"PRODUCTCODE" VARCHAR(7)GENERATED ALWAYS AS('PC-'||REPEAT('0',4-CHAR_LENGTH( CAST( "PID" AS VARCHAR(10))))|| CAST("PID" AS VARCHAR(11))),"TEXT" VARCHAR(10))

Now once Menue View, refresh Tables and you will find the table TBL_AUTO_PRODUCTCODE, where you can type in TEXT, PID and PRODUCTCODE will be generated.
R

[gkick]

That s great, thanks always good to have options

GK