BASE - Relationships - Error -170? Primary or unique constraint required

[WeebleSue]

I have a very simple database. I’m trying to link 2 tables together so that when I am looking at the MainList table, I can select the Category from the table CategoryList. Very, very simple.

I am trying to link the “category” column of my main table with the “category1” column of my category table. I have an extensive MS Access background, and I cannot figure this out to save my life. No matter what I do, I get the error:
SQL Status: S0011
Error code: -170

Primary or unique constraint required on main table: “MainList” in statement [ALTER TABLE “CategoryList” ADD FOREIGN KEY (“Category1”) REFERENCES “MainList” (“Category”)]

I have searched Google, this forum, and the help guides. While some posts here are related, I am totally lost reading the answers. Just tell me what I have to do. I don’t need a programmer’s explanation of the error.

Both tables have Primary Keys defined as Integers. They all have unique names. The big Base Guide says that the Foreign Key will be created in the linked table when I join the two column names together. It does not. I only get the error.

Do I have to create the Foreign Key myself in the CategoryList table?

I created this database by importing a spreadsheet into my MainList table, so the “Category” column of my main table is filled out already. All values exist in the CategoryList table. I wouldn’t get this error for a mismatch, though. I manually entered the values in Category List.

I am using LibreOffice 26.8.03 on Windows 11.

Thank you…
WeebleSue

[CRDF]

What is the type of “MainList”.“Category”?
In the relationships diagram did you drag
“CategoryList”.“ID_cat” → “MainList”.“Category”
?
E.g:

CREATE TABLE "MainList" (
    ID INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    "Category" INT NOT NULL,
    "Description" VARCHAR(100),
    CONSTRAINT FK_CAT FOREIGN KEY ("Category") REFERENCES "CategoryList"("ID_cat")
)

[Villeroy]

The attached file demonstrates a one-to-many relation (1-n) between persons and pets where each person has one animal and a many-to-many relation (m-n) between persons and tools where each person can have zero, one or many tools.
Any item (person, pet, tool) is identified by an automatic ID number. Everything is connected by these ID numbers, for instance Person #5 related to Animal #7 having Tools #1, #3 and #7. Any other attribute of these items (name, birthday, size, color, whatever) must not be referenced. While everything is referenced by the integer item-IDs, they do not appear in the user interface (Base forms and reports). The IDs serve an entirely technical purpose pointing to one distinct item.
Many-to-many relations between 2 items require a 3rd table mapping the item IDs to each other.
In the relations window, each line points to one unique ID (the 1-side of a 1-n relation) and a foreign key on the other side (the n-side). Each foreign key value points to one distinct item ID of another table.
relations2listboxes.odb (93.8 KB)

[WeebleSue]

MainList.Category is a simple text column. CategoryList.Category1 is a simple text column.

I dragged Category1 onto Category. Same error.
I dragged Category onto Category1. Same error.
I dragged ID_Cat onto MainList.just-about-everything. Same error.
I dragged MainList.ID onto CategoryList.just-about-everything. Same error.

No matter what I drag from one table to the other, same error. Nothing works.

I have attached a copy of my database so you can try it out yourself.
Train List.odb (59.5 KB)

[WeebleSue]

Thank you… as I said I am very well versed in table relationships in MS Access. Unfortunately this does not explain why it isn’t working in LibreOffice BASE.

In MS Access, I drag one field onto another and get the pop up window asking what kind of relationship I am setting up. All the foreign_key stuff is done behind the scenes. No new columns are added to my MainList table there once I have made a relationship. I’m not sure why LibreOffice requires this.

All I get in LibreOffice BASE is the error message with no explanation as to what the error means or how to fix it. Thank you for your response but it was not helpful in guiding me to fix the error in LibreOffice BASE.

[RobertG]

Have had a look.

1. Fields, which should be connected, should always have the same type. “Category” is varchar, same content as “Category1”. You need a field like “CategroyList_ID”, which is Integer. So you could connect this Integer field with the field “IC_cat” of “CategoryList”.
2. There are other tables with the same problem: Text content is the same as the content in the separate tables. Change to Fields with Integer values.
3. “Type” shouldn’t appear in the Main Table. Only “SubType_ID” should appear. Every “Type” will be connected to the main table by the SubType.

Have done all this, connected the tables, update the tables by SQL.
.
Note:

• No primary key is set as auto value. Choose this for better input new rows to the tables, special the “MainList”.
• Fields “Category”, “Material”, “Type” and “Subtype” could be removed from “MainList”. But first have a look if all fields in “Category” will show a value in “CategoryList_ID” and also for the other fields.
  Train List.odb (73.1 KB)
  Please have a look at Base Guide. It’s an old version, but all newer version only exist in German language.

[CRDF]

@WeebleSue

I dragged Category1 onto Category. Same error.

CategoryList pk is ID_cat, not Category1

@WeebleSue

I dragged ID_Cat onto MainList.just-about-everything.

ID_cat = INTEGER
“MainList”.“Category” = VARCHAR
 
As your categories are very simple production status, you CAN have a text pk. It’s legitimate to have short varchars as pk. But then CategoryList will have just the Category1 column = VARCHAR pk.

@WeebleSue

in LibreOffice BASE.

Please take notice that LO Base is just a front end for some DB.
And also note that HSQLDB 1.8 that you are using is an obsolete, deprecated engine.

[WeebleSue]

This is good - thank you!

But I’m still confused. My MainList now has columns that have integers in them. I don’t want the integers. I want the word that goes along with that integer. How do I show the words?

[CRDF]

@WeebleSue

How do I show the words?

EDIT: now I notice @RobertG had already sent the adaptation, but to not lose my “work”

1. The “normal” way: via the queries or views.
2. You CAN have the Categories Names (words) as pk as I said above. It’s a legitimate solution, particularly in your case as the names are very short.
   BUT you can’t e.g. change these names!

Sample Firebird Embedded with tables CategoryList and MainList with column CID INTEGER fk added.
Inserted Unfinished into the NULL rows.
Now of course drop column Category from the table.
For the real thing use external (not embedded) mode!

 
Trens.odb (18.2 KB)

[Villeroy]

@WeebleSue

ALTER TABLE “CategoryList” ADD FOREIGN KEY (“Category1”) REFERENCES “MainList” (“Category”)]

MainList.Category is not enforced to be unique, even if it happens to be actually. MainList.Category needs to be either a primary key or a unique constraint.
In the latter case:

ALTER TABLE "MainList" ADD COSTRAINT "Unique_Category" UNIQUE ("Category")

[CRDF]

Also DateDone should be DATE.
As only month is relevant, enter 01 as day…

[RobertG]

@WeebleSue

I don’t want the integers. I want the word that goes along with that integer.

Then: You don’t need any other table except MainList. You could enter the values for “Category”, “Material” and so on by comboboxes in a form and don’t need any relation.
.
If you want to use a relations in different tables you need to save the foreignkey in MainList, not the text content. You will get the text content in queries or in the form by listboxes (connect to foreignkey/number - show text content).