Page 1 of 1

[Solved] How to assign autovalue for PK in linking table

PostPosted: Thu Aug 22, 2019 1:10 pm
by sjskhalsa
I a previous post https://forum.openoffice.org/en/forum/viewtopic.php?f=13&t=97856 I asked how to create a linking table, and then how to create a form that would use the linking table, and received valuable instructions.

However, in entering data I got errors about non-unique entries in the linking table, presumably because the linking table needed an index itself. So, I created the linking table with:
Code: Select all   Expand viewCollapse view
CREATE TABLE "Source_Concept" (LinkID INT, SID INT, CID INT, PRIMARY KEY (LinkID,SID,CID), FOREIGN KEY (SID) REFERENCES "Sources" ("IDSource"), FOREIGN KEY (CID) REFERENCES "Concepts" ("IDConcept"));

However, when I try to assign autovalue to LinkID, so that when an entry is created via a subform from the concept or source form, it gives an error because the primary key doesn't get a value. Here's the relationship diagram:
Screen Shot 2019-08-22 at 12.53.14 PM.png

Re: How to assign autovalue for PK in linking table

PostPosted: Thu Aug 22, 2019 3:21 pm
by keme
Why is it required to have a composite primary key here? Is it possible to have duplicate LINKID values?

If you need to autogenerate a part of a composite key, it may be possible through creating a value sequence in a separate table, or perhaps on the fly as a cross join query, and pick an available value from that. Probably inefficient, and in most cases I would guess that a DB redesign is required rather than "partial autogeneration".

If there will only be one Source_Concept record for each LINKID value, you can use that field alone as the PK and there will not be an issue with autovalue. You can create an index for the combined fields if that is needed.

Re: How to assign autovalue for PK in linking table

PostPosted: Thu Aug 22, 2019 3:24 pm
by Villeroy
Drop the LinkID. You only need the 2 foreign keys which will serve as compound primary key as well. The foreign key SID depends on the "Sources" table's primary key, the foreign key CID depends on the "Concepts" table's primary key and both keys SID and CID together build the linking table's primary key which means that you can not enter the same combination of SID and CID twice. Each SID belongs to some CID once and each CID belongs to some SID only once.

Re: [Solved] How to assign autovalue for PK in linking table

PostPosted: Thu Aug 22, 2019 7:25 pm
by sjskhalsa
I didn't realize a table could have a compound PK. Thanks. Problem solved.