Primary Key composed from three fields can't be created

English
#1

I’m creating a (part of my) database which needs a junctionTable. The intention is to have a junction Table which makes every single row in tAssignment unique. Two fields in tAssignment refer to other tables, tCampaign and tProspect. The field assignmentID should be of type autovalue and will be referred to from the tEffort table, which is meant to record all activities within the campaign for a certain prospect.

The attached sketch shows the situation.

LO-Structure
LO-Structure1012×677 36.3 KB

(Graph changed because of a wrong path)

However, no matter what I try, I cannot get these three INTEGER fields to become one primary key. The error message when saving the table reads

Attempt to define a second primary key in statement [CREATE TABLE "tAssignment" ("assignmentId" INTEGER NOT NULL IDENTITY,"campaignRef" INTEGER NOT NULL,"prospectRef" INTEGER NOT NULL, PRIMARY KEY ("assignmentId","campaignRef","prospectRef")]

and after hours of tweaking and studying other people’s work with LO, I’m left clueless about this.

Question:
How do I create a composite primary key from three INTEGER fields?

Thanks a lot in advance,
Norbert

Version: 24.2.5.2 (X86_64) / LibreOffice Community
Build ID: bffef4ea93e59bebbeaf7f431bb02b1a39ee8a59
CPU threads: 8; OS: macOS 11.7.10; UI render: Skia/Raster; VCL: osx
Locale: de-AT (de_AT.UTF-8); UI: en-US
Calc: threaded

#2

An autovalue is unique. So if you create an autovalue it will be the only key value.
You could create a table with many key values:
Mark the fields in table editor.
Right mouse button → Primary key.
Will work if there isn’t already a primary key as autovalue.

In you example effortID would be an autovalue. So it will be unique. And assignmentID wont be an autovalue.

1 Like
#3

Oops – my bad, the sketch has an error: effortID is the primary key in tEffort and will be used for constraints down that pipe. Beneath effortID in the tEffort table I entered assignmentRef which is meant to be the foreign key referring to assignmentID. The reference should go from there to assignmentID.

Users of “tEffort” shall be set up with one "ready to use tEffort table which should become the first entry of a series of tEffort rows, one per interaction per prospect and campaign. When the assignmentID doesn’t work with autovalue, I have no means to provide a unique chain of efforts per campaign:prospect pairing.

Well, maybe I’m already overthinking things, I spent too much time on this issue and maybe I don’t get the obvious. Thanks for your help,
Norbert

PS.: Just corrected the sketch

#4

The relation design window is limited. Do it in plain, simple SQL.
Remove all lines in the relations window and do it like this:

alter table "tAssignmenr" add foreign key ("assignmentID") references "tEffort" ("effortID");

The field names in braces are lists of fields. There may be more than one.

#5

… and UNIQUE fields: You could create a new index for your table, define the fields for this index and set it to unique.

2 Likes
#6

alter table "table name" add constraint "my_triple_index" unique ("field1", "field2", "field3");

2 Likes
#7

Not being familiar with HSQLDB I followed the hints in the comments and ended up with these statements working:

DROP TABLE "tAssignment" IF EXISTS;

CREATE TABLE "tAssignment" (
"assignmentID" INTEGER IDENTITY PRIMARY KEY,
"campaignRef" INTEGER, 
"prospectRef" INTEGER
);
ALTER TABLE "tAssignment" 
ADD CONSTRAINT "uniqueAssignment" UNIQUE ("campaignRef", "prospectRef" );

It took several runs to get me to this state.
One of the tries was to have all three columns in this statement:

ALTER TABLE "tAssignment" 
ADD CONSTRAINT "uniqueAssignment" UNIQUE ("assignmentID", "campaignRef", "prospectRef" );

This however didn’t warrant uniqueness to the rows. As I supposed including the primary key with autovalue in this constraint might always yield unique rows, I removed it for the ‘final’ version without making any difference.

State:
Primary key and autovalue are established with the most important field assignmentID. There is nothing which makes me believe this might be a composite primary key however. Trying

ALTER TABLE "tAssignment" 
ADD CONSTRAINT "pkAssignment" PRIMARY KEY ("assignmentID", "campaignRef", "prospectRef");

1: Attempt to drop the primary key …

was no success either.

As I spent way too much time on this already, I decided to live with the issue for the time being. As I am doing these exercises on HSQLDB this should suffice. Once I’m considering going live with this application, I’m looking forward to installing the database which fits my needs best, probably POSTGRES, as I learned from reading the hints from Ratslinger. As I’m running a great deal of MariaDB based sites I have yet to evaluate whether it pays to obtain one more database infrastructure.

I hope that my final report didn’t violate any rules or guidelines I haven’t read as of yet, and I hope some lost soul might find these lines helping it out when the night is darkest :wink:

Thank you all for your hints which got me on the right track, and just in case you’ll find some more regarding this topic, please don’t hold your breath.

#8

Still researching the case I found this example on stackoverflow.
I dropped the SQL shown there on the SQL dialog of Base and sure enough got the same error message

Attempt to define a second primary key in statement

Makes me wonder whether there’s a way to issue SQL against the database without involving the limiting Base frontend? But after all, I suppose it’ll be best to go for a more robust database I can tweak from the CLI, right?

#9

It has nothing to do with Base. With HSQL, any auto-ID field implies that this is also the primary key. With Firebird, for example, auto-increment fields can exist without being primary keys.

#10

Have you read the linked example? In full?
Namely this (solution!)

CREATE TABLE sample_table 
( 
  hash  VARCHAR(20), 
  id    INTEGER GENERATED BY DEFAULT AS IDENTITY, 
  data  VARCHAR(256), 
  PRIMARY KEY(hash, id) 
);
#11 
create table "tProspect"( "Name" VARCHAR(20) not null, "prospectID" INT IDENTITY);
create table "tCampaign"( "Name" VARCHAR(20) not null, "campaignID" INT IDENTITY);
create table "tAssignment"("assignmentID" int identity, "prospectRef" int not null, "campaignRef" int not null, 
  foreign key ("prospectRef") references "tProspect" ("prospectID"),
  foreign key ("campaignRef") references "tCampaign" ("campaignID"));
create table "tEffort"( "effortID" INT IDENTITY, "assignmentRef" int not null, "effortRef" int not null, 
  foreign key ("assignmentRef") references "tAssignment"("assignmentID"), 
  foreign key ("effortRef") references "tEffort" ("effortID"));

generates this embedded HSQL database, which makes perfect sense to me:
Bildschirmfoto von 2024-08-16 13-04-25

#12

This of course makes sense – it’s what I came up with yesterday. However, not having a composite PK in tAssignment will allow non-unique rows such as:
0 1 1
1 1 1

That’s my problem, and it isn’t solved as of yet.

In the same sense, tEffort also needs a composite primary key to avoid the same tuple campaign and prospect, represented by assignment, to be added to the table more than once.

#13

Ignore any limiting Base frontend and use the command line. Every statement you run in the SQL window will be executed by the actual database engine (HSQL 1.8 in this case),

If you want to use your database with other frontends, just extract the database with a zip program and rename the files (script → myDB…script, properties → myDB.properties etc.).

Apache OpenOffice Community Forum - [Python] Macro to extract and reconnect embedded HSQLDB - (View topic) does this for you and reconnects the Base document to the extracted database.
There are 2 methods to connect with an external HSQLDB:

  1. jdbc:/hsqldb:file:/path/myDB
#14

Why is that a problem? The auto-incrementing field is the primary key. Without the other two fields, it is unique by design.
For unique triple combinations of assignments, prospects and campaigns you need a third table with a triple non-automatic PK collecting the 3 IDs from the other tables.

create table APC(AID int not null, PID int not null, CID int not null, 
  primary key(AID, PID, CID),
  foreign key (AID) references "tAssignment" ("assignmentID"),
  foreign key (PID) references "tProspect" ("prospectID"),
  foreign key (CID) references "tCampaign" ("campaignID"));

and drop the foreign keys in the assignment table.

alter table "tAssignment" Drop constraint "SYS_FK_53"
alter table "tAssignment" Drop column "prospectRef"
alter table "tAssignment" Drop constraint "SYS_FK_54"
alter table "tAssignment" Drop column "campaignRef"

Now I have

Bildschirmfoto von 2024-08-16 16-36-25
Bildschirmfoto von 2024-08-16 16-36-25550×553 33.6 KB

You can add some assignment date, description etc. at any time. The important thing at this stage is the web of relations.

1 Like
#15

Great, thanks. And maybe implicitly the reason for the limiting behavior, as the most current HSQLDB is 2.7.3 already. So maybe the HSQLDB manual is way ahead of the version used by LibreOffice.

#16

The wizardry sounds interesting, I have yet to try this myself. However, I can’t see a difference between the two graphs you posted, hence I cannot follow your “Now I have…” expression – you had the same already.

However, I just tried my solution again, and it works as expected. Seems I changed something I’m not aware of in the morning. Finally, problem solved, looking forward to proceed with my task.

Thanks a lot for shining light on all things HSQLDB.

#17

The embedded HSQLDB is version 1.8 of 2006 as documented here: Chapter 9. SQL Syntax

I clicked the wrong file. Fixed it now.

#18

Yup, this is indeed inspiring. I guess this architecture will come in handy when I have to tackle international postalCodes, which get pretty tough when handled correctly.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.