[Solved] Additional auto increment on no pk

Creating tables and queries

[Solved] Additional auto increment on no pk

Postby gkick » Tue Nov 05, 2019 6:13 pm

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
Last edited by gkick on Thu Nov 07, 2019 3:36 am, edited 1 time in total.
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 207
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Additional auto increment on no pk

Postby F3K Total » Wed Nov 06, 2019 7:29 pm

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"
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 962
Joined: Fri Dec 16, 2011 8:20 pm

Re: Additional auto increment on no pk

Postby gkick » Thu Nov 07, 2019 3:35 am

@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
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 207
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: [Solved] Additional auto increment on no pk

Postby F3K Total » Thu Nov 07, 2019 7:02 pm

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
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 962
Joined: Fri Dec 16, 2011 8:20 pm

Re: [Solved] Additional auto increment on no pk

Postby gkick » Fri Nov 08, 2019 5:12 am

That s great, thanks always good to have options

GK
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 207
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 2 guests