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

[sjskhalsa]

I a previous post 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

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:

[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.

[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.

[sjskhalsa]

I didn't realize a table could have a compound PK. Thanks. Problem solved.