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

Discuss the database features

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

Postby sjskhalsa » Thu Aug 22, 2019 1:10 pm

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
Last edited by sjskhalsa on Thu Aug 22, 2019 7:22 pm, edited 1 time in total.
OpenOffice 4.1.5 on MacOS 10.14.4
sjskhalsa
 
Posts: 9
Joined: Mon Apr 29, 2019 1:56 am

Re: How to assign autovalue for PK in linking table

Postby keme » Thu Aug 22, 2019 3:21 pm

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.
User avatar
keme
Volunteer
 
Posts: 3371
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: How to assign autovalue for PK in linking table

Postby Villeroy » Thu Aug 22, 2019 3:24 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28552
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby sjskhalsa » Thu Aug 22, 2019 7:25 pm

I didn't realize a table could have a compound PK. Thanks. Problem solved.
OpenOffice 4.1.5 on MacOS 10.14.4
sjskhalsa
 
Posts: 9
Joined: Mon Apr 29, 2019 1:56 am


Return to Base

Who is online

Users browsing this forum: No registered users and 2 guests