I already have a primary key in the table - but need one of the fields to generate an autovalue to be unique. However I get an error as I cannot have two primary keys.
Hello,
Can you please provide more description on what is needed and why. This sounds like a possible design problem.
Please do not use an Answer but either use add a comment or Edit your question with the additional information.
You also do not mention what database or LO version you are using. May be helpful.
I need to create a table which has a primary key customer ID and I also need to have an automatically increasing integer field in the table. But dbms automatically makes it a key, I do not want it to be a primary key.
Using LO Version: 6.0.7.3
Build ID: 1:6.0.7-0ubuntu0.18.04.10
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3;
Locale: en-ZA (en_ZA.UTF-8); Calc: group
Using HSQLDB Embedded as database.
There is still no explanation as to what you are doing. Yes you have now stated multiple times you want a incrementing unique number. I cannot imagine why if your DB is constructed properly. This is why I have asked what it is needed for and why. My contention is that it doesn’t belong in the same table but rather in another.
HSQLDB embedded is very old (v1.8) and is limited in capabilities. It also has the possibility of losing data if not handled correctly.
Of course you can always write a macro to increment your own field as initially outlined in answer by @gkick.
Sorry, I am a newby, trying to convert a access DB to be used in LO.
Which DB would you recommend?
Recommending a DB is not wise. Choice should be because of the needs you have and not what I use and need. There are many choices including splitting the HSQLDB and using a newer version. See →
[Wizard] Create a new ‘split’ HSQL 2.x database.
There is also PostgreSQL, MariaDB, MySQL, Firebird Server, Firebird embedded, SQLite, and many others you can connect to.
Hi,
one option is to use a variable in a macro
Sub incrementMe
oConn = ThisDatabaseDocument.DataSource.getConnection("","")
SQL = "UPDATE ""mytable"" SET ""myfield"" =""myfield""+1 WHERE ""otherfield"" = 'xyz' "
oQuery = oConn.createStatement()
oQuery.executeQuery(SQL)
End Sub
run on the forms on open document or other event
While I understand the concept, this does not get the max value for the wanted field. Would be better to retrieve max value existing and increment that. However may even be better off with a sequence generator. Don’t know. OP has left out basic information.