Base n:m relationships HSQLDB using intermediate table - what am I missing?

[Inkblotr]

I have an “ItemMaster” table with 2 fields:
Item_ID - Integer, AutoValue, Primary Key.
Item_Name - VARCHAR, Entry req’d Yes.

Second table “Types” with 3 fields:
Type_ID - Integer, AutoValue, PrimaryKey.
Type_Abbrev - Text (fix), Entry req’d Yes.
Type_Name - VARCHAR, Entry req’d Yes.

Intermediate table (trying to follow Base Handbook Media example) “rel_Item_Types” with 2 fields:
Item_ID - Integer, Entry req’d Yes.
Type_ID - Integer, Entry req’d Yes.

In the Tools/Relations window I have set the ItemMaster/Item_ID relationship to the rel_Items_Type/Item_ID as a 1:n. I also set the Types/Type_ID relationship to the rel_Items_Type/Type_ID as a 1:n.

Situation: For each ItemMaster record, there will be 1 to many Types records.

1. I am unable to set both fields in the “rel_Item_Types” table as PrimaryKeys (setting one field is possible through the GUI). The 2 fields together WOULD be a unique key.

2. I am unable to manually enter any records in the “rel_Item_Types” table (I think because there is no PrimaryKey field, but not sure).

I am new to LO Base - last database system I used was dBase/Clipper/FoxPro MANY years ago!
I can provide a simple odb file example if that would help.
Thank you in advance!

[Ratslinger]

Hello,

Unable to locate where in Media example you are following. In future when mentioning a sample or post, please provide a link/chapter/page - just something to see what it is. Don’t make us guess & search.

Now your question has some confusion. You state:

Situation: For each ItemMaster record, there will be 1 to many Types records.

If that is the case then an intermediate table is not needed. You simply give a reference in each ‘Types’ record back to the ‘ItemMaster’ record.

You would use an intermediate table in a many-to-many situation (not common). This table would have its’ own record key (usually auto-increment Integer) a field to point back to the Master and a field to point to the Other table. Depending on the situation other fields may be present.

Edit:

Still not certain you are wanting a many to many relationship but will go with what you asked for. However, please see my answer in this post for a simple view of both one-to-many and many-to-many → Should I use a separate table?.

Your example data does not really help. It also leads me to believe you are not quite grasping the method necessary.

Again with your question and based on the method used in the manual (thank you - would not have easily guessed without your information), you can have multiple fields (columns) which comprise the key. In table edit, select all needed (use Shift or Ctrl key for multiple selection) then right click & select Primary Key.

Attached is a sample with the tables doing this. Relationship is also set but there are no forms. This requires knowledge of just what is being done and how you may want to do something (list boxes certainly come to mind).

Sample ----- ManyToMany.odb

[Inkblotr]

My sincere apologies, I was not intentionally leaving out information. The “Media” example I referred to is the example system in the Base Handbook, ver 5.0, Chapter 3, Tables, Pages 5 and 6, section “Many-to-Many Relationships”. Link: https://wiki.documentfoundation.org/images/5/59/BH5003-Tables.pdf
The intermediate table in the Handbook example is named “rel_Media_Author” is does not show to have an auto-incrementing integer key. Downloading the example files and opening them in Base clearly shows a table with 2 primary key fields.

Trying to more clearly communicate the “Situation”: there will be hundreds of ItemMaster records, each record will need from 1 to n “Types” records to be associated. Examples of records in the “ref_Item_Type” table would look like the following (sorted):
Item_ID Type_ID
0 3
0 12
0 14
1 2
1 8
2 0
2 3
2 14
2 19

[Inkblotr]

Ugh, the examples of records didn’t keep the formatting I’d hoped above, please ignore.

[Inkblotr]

The example files I mentioned above are available for download at this link:
https://wiki.documentfoundation.org/images/5/52/Sample-databases.zip

The last paragraph of the answer above talks about what I’m trying to accomplish. Using the example database, I can manually enter new records in the “rel_Media_Author” table and it does not have an auto-incrementing field… I’m asking for help to setup something almost identical.

Again, thank you in advance for any/all help!

[Ratslinger]

Please see edit in answer.

[Inkblotr]

YES!! @Ratslinger, you gave me the missing piece of information I needed - select both ID fields in the “rel_Item_Type” intermediate table before right clicking and clicking “Primary Key”!! With only one field flagged as a primary key and no unique field in the table, you cannot create/add records to the table. With both flagged as primary key, I can create records!

I take responsibility for failing to effectively communicate what I needed. Thank you for sticking with me!

[Ratslinger]

@inkblotr Just a follow up. If you look in the Base Handbook, ver 5.0, Chapter 3, Tables, Page 15 (Primary Keys section), it states there just what I had mentioned about selecting multiple fields. Yes, sometimes the answer is not obvious in the documentation and may be difficult to locate but typically it is there.