Associative Table error in LibreBase and PostgreSQL

[Tyler1]

LO 5.1.4.2, PSQL 9.5., OS Win7
I have a PostgreSQL database and I am trying to setup a LibreBase front end. I am getting an error when I try to add an address relation to a form entry in my wotbl. The wotbl stores work order information part of the information is an address, but some work orders need to be linked to 2 addresses so I have a listbox drop down setup and an associative table relationship to produce a many to many join (Just like the movies genre example video that I did get working thanks for the help). So the associative table is jtbl_cwo (UNIQUE_ID, woid) where UNIQUE_ID is the address list pk (civictbl) and woid is the work order pk (wotbl); together they are the Composite PK for jtbl_cwo.
So the setup worked in HSQL but to setup in postgre I needed to include the joins in the SQL statement for the form,

(SELECT “civictbl”.“ADDRESS” FROM “wotracking”.“jtbl_cwo” “jtbl_cwo” FULL OUTER JOIN “wotracking”.“wotbl” “wotbl” ON “jtbl_cwo”.“woid” = “wotbl”.“woid” FULL OUTER JOIN “wotracking”.“civictbl” “civictbl” ON “jtbl_cwo”.“unique_id” = “civictbl”."unique_id)

I get a drop down but when I try to apply the selection I get a postgre error;

pq_driver: [PGRES_FATAL_ERROR: insert or update on table “jtbl_cwo” violates foreign key constraint “jtbl_cwo_unique_id_fkey” DETAIL: Key (unique_id)=(0) is not present in table “civictbl”. (caused by statement 'UPDATE “wotracking”.jtbl_cwo" SET “unique_id” = ‘0’ WHERE “unique_id” = ‘5002084’ AND “woid” = 'BB306-16")

It seems to me that it is trying to set the unique_id in the jtbl_cwo to 0 when it should be set to 5002084 but I cannot find where in either the Libre SQL statement or in PostgreSQL how to use the unique_id from the civictbl. I have auto assign and auto increment off I believe.
Do I need to build a trigger in PostgreSQL?
Sorry if I am not clear I do not know all the lingo very well I am not a programmer by trade; I am familiar with databases and their design but by no means am I an expert on these things.
Thanks hope someone can help;
Tyler

[Ratslinger]

The error is correct for the SQL you are using. See this link (click here). It is trying to update an existing record in table jtbl_cwo field unique_id.

I believe you are trying to ADD a new record to that table. You should be using an INSERT statement (click here).

The statement should be something like:

INSERT INTO "jtbl_cwo" VALUES ('5002084', 'BB306-16');

This, of course, depends upon your table construction. You also need to check for errors since this record may already exist.

[Tyler1]

LibreBase seems to decide when to use INSERT or UPDATE. When I used a new wotbl entry I got the exact same error only with INSET instead of UPDATE and ‘0’ instead of ‘5002084’ when the unique_id was suppose to be ‘5002084’ (The address I am testing this with uses Unique_ID 5002084). I think the reason why it was using update is because I tried to wrote the unique_id into the table manually to see if it would produce the desired relationship.

[Tyler1]

Ok the problem is “Me”; After reading those links and looking at what was going on I realized that the statement I wrote to create the associative relationships was flawed… severely… and in fixing it the issue seemed to resolve itself.
Thanks again though, your comment really did point me in the right direction.