Page 1 of 1

[Solved] Additional auto increment on no pk

PostPosted: Tue Nov 05, 2019 6:13 pm
by 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

Re: Additional auto increment on no pk

PostPosted: Wed Nov 06, 2019 7:29 pm
by 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   Expand viewCollapse view
SELECT "PID", 'PC-' || REPEAT( '0', 4 - LENGTH( "PID" ) ) || "PID" "PRODUCT" FROM "Table1"

Re: Additional auto increment on no pk

PostPosted: Thu Nov 07, 2019 3:35 am
by 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

Re: [Solved] Additional auto increment on no pk

PostPosted: Thu Nov 07, 2019 7:02 pm
by 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   Expand viewCollapse view
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

Re: [Solved] Additional auto increment on no pk

PostPosted: Fri Nov 08, 2019 5:12 am
by gkick
That s great, thanks always good to have options

GK