[Solved] Cannot save several relationships between 2 tables

[Skrag]

Hey guys,

I am working on a project classifying conditionals (e.g. "If he woke up early, he is productive right now"). A lot of properties apply to the antecedent ("He woke up early") and the consequent ("He is productive right now") separately. The tense, for example, is present for the antecedent and past for the consequent. So I want two relationships between the table containing tenses and the table containing the conditionals: One between the tenses and the tense of the antecedent and the other between the tenses and the tense of the consequent. The same for other properties.

However, OpenOffice won't save both relationships in all cases. The first one it usually allows, when trying to enter the second it asks if I really want to add a new relation or alter the existing one. If I add the new one, it graphically appears. If I save and close the relationship design window and open it again, the second relationship is gone. Weirdly, in some cases this actually works, in others it doesn't. I haven't figured out any differences between these two cases. See the appendix for a Screenshot. tblTense and tblComplexity have both relationships, I tried it several times with tblOvertModal, it just won't work.

I would be glad if anyone could help!

Best, Skrag

[UnklDonald418]

It sounds like you are trying to establish a many to many relationship. Databases can only directly reference a one to many (1 to n) relationship. Many to many relationships require an intermediate table.
If you are not familiar with intermediate tables look at
viewtopic.php?f=100&t=40444
the download there “relations2listboxes.odb ” provides a working example.

[Skrag]

Thanks for your answer! No, this has nothing to do with establishing a many to many relationship. I do not have trouble with that. Instead, I want to establish a relationship between a primary key and a field, as well as another relationship between the same primary key (which does not happen in case of many to many relationships) and a different field in the same table as the first field.

The screenshot shows what I want to do: tblTense and tblAspect both have the kind of relationship I want to establish. The same thing just doesn't work with tblOvertModal.

[Arineckaig]

I want to establish a relationship between a primary key and a field, as well as another relationship between the same primary key ..... and a different field in the same table as the first field.

If more than one Foreign Key in one table were to reference the same Primary Key in another table they would have to hold the same value to ensure consistency in which case all but one of the the Foreign Keys would be redundant. Hence the Base GUI tries to prevent this conflict with the principles that govern a Relational Database. The phrase 'one-to-many' means a single RECORD can reference one or more RECORDS.
Later edit: After further consideration I suspect I seriously misunderstood the diagram, the description of the database and was further confused by the dismissal of a possible many-to-many relationship between the records in the central table and some of the other tables: hence my deletion of a later post. Many apologies for my over hasty reading and response to the issue.

[Skrag]

If more than one Foreign Key in one table were to reference the same Primary Key in another table they would have to hold the same value to ensure consistency in which case all but one of the the Foreign Keys would be redundant. Hence the Base GUI tries to prevent this conflict with the principles that govern a Relational Database. The phrase 'one-to-may' means one RECORD references many RECORDS.

Thank you, that seems to make sense. So two question remain for me:

1) How come that tblTense, tblComplexity and tblAspect seem to violate that rule, i.e. could make the database inconsistent?

2) How can I achieve what needs to be done? Do I have to duplicate the table with the primary key in order to have two separate primary keys, each for every foreign key?

[UnklDonald418]

As Arineckaig pointed out we don't have a very good understanding of what you are doing, but if the other relationships actually work then perhaps you could use SQL to add the missing one. Something like:

Code: Select all

ALTER TABLE "?" ADD CONSTRAINT FK_TLB_OMOD FOREIGN KEY ("OvertModalConsequentID") REFERENCES "tblOvertModal" ("OvertModalID");

Since your screen shot cropped off the main table name you will need to edit the command and at the very least add the table name.

[UnklDonald418]

After further consideration I'm wondering if you need to establish those relationships at all.
You might be able to use list boxes on a form to select and display the values in those tables.

[Arineckaig]

The Relationship Design module can also fail if the selection of a Foreign Key is not the same datatype as the Primary Key it is to reference.

[keme]

Relationships are useful in this case to ensure data integrity/consistency. What you try to do should work. Differing data types has already been mentioned as a possible cause. Another one that I can't see mentioned:

Do you have content in your tables when you make the relationship? May there be data which does not comply with the restriction imposed by the relationship? (OvertModalConsequentID used in your main table where the corresponding entry in tblOvertModal - with the same OvertModalID value - does not exist.)

[Skrag]

Relationships are useful in this case to ensure data integrity/consistency. What you try to do should work. Differing data types has already been mentioned as a possible cause. Another one that I can't see mentioned:

Do you have content in your tables when you make the relationship? May there be data which does not comply with the restriction imposed by the relationship? (OvertModalConsequentID used in your main table where the corresponding entry in tblOvertModal - with the same OvertModalID value - does not exist.)

I am just getting started with OO Base, before I used MS Access. In Access, relationships like these were no problem. Interestingly, when I established a connection like this in the relationship design window, Access doubled the table with the primary key and gave the second table a different name (like "tblXYZ_1"). I don't know if Access actually created a second table or if this was just how this relationship was shown in the relationship design windows. But basically, it should be possible to do this.

I checked both on the data types and on the referential integrity restriction. The data types match and the table with the foreign key (tblExample, see below) is empty in the relevant fields. So this should be no problem.

As Arineckaig pointed out we don't have a very good understanding of what you are doing, but if the other relationships actually work then perhaps you could use SQL to add the missing one. Something like:

Code: Select all

ALTER TABLE "?" ADD CONSTRAINT FK_TLB_OMOD FOREIGN KEY ("OvertModalConsequentID") REFERENCES "tblOvertModal" ("OvertModalID");

Since your screen shot cropped off the main table name you will need to edit the command and at the very least add the table name.

OK, I started a test-database to make stuff more clear. If you have the time, you might try the same, since this seems to be a basic problem, maybe it has something to do my version or my platform or whatever. This is my relationship design window:



All tables are empty, all fields in tblConditional and all primary keys are of data type Integer. Those matters shouldn't matter.

Now check out this topic viewtopic.php?f=13&t=65243, where Sliderule suggests a code for retrieving a list of all foreign keys:

Code: Select all

SELECT 
   T.TABLE_NAME, 
   T.TABLE_TYPE, 
   C.FK_NAME AS "FOREIGN KEY NAME", 
   C.PK_NAME, 
   C.PKTABLE_NAME, 
   C.PKCOLUMN_NAME 
FROM INFORMATION_SCHEMA.SYSTEM_CROSSREFERENCE AS C, 
     INFORMATION_SCHEMA.SYSTEM_TABLES AS T
WHERE C.PKTABLE_SCHEM = 'PUBLIC' 
  AND C.FKTABLE_SCHEM = 'PUBLIC' 
  AND C.FKTABLE_NAME = T.TABLE_NAME 
Order By T.TABLE_NAME, 
         C.KEY_SEQ

I ran the code in direct mode and got this:



So the code works in showing the correct foreign keys. Regrettably, it does not show the column name of the foreign key. If you have suggestions how to accomplish this, I would be glad. Now I wanted to try out how UnklDonalds suggestion would work and added a relationship between tblConditional and tblAspect. Note that this is not the problematic one, I could have done this with the relationship design window as well. So I ran

Code: Select all

ALTER TABLE "tblConditional" ADD CONSTRAINT FK_XYZ FOREIGN KEY ("AspectAntecedent") REFERENCES "tblAspect" ("AspectID")

The result is this:



So the foreign key was successfully added. Regrettably, this relationship does not show in the relationship design window. But if I want to add the same relationship in the relationship design window, it produces the error that the relationship already exists.

The problem described above still holds: I cannot add a second relationship between the primary key of tblTense and the field "TenseConsequence" in tblConditional in the relationship design window. Despite saving, it does not show the relationship in the relationship design window once I close and open it again. Running the command to list the foreign keys yields the same result: It does not show the desired relationship in the list. Now I tried this code to implement the desired relationship via SQL:

Code: Select all

ALTER TABLE "tblConditional" ADD CONSTRAINT FK_TENSE_2 FOREIGN KEY ("TenseConsequent") REFERENCES "tblTense" ("TenseID")

The result is this:



So it seems to have added the foreign key successfully, although it still does not show in the relationship design window.

I checked if the relationships do what they are supposed to do in a query: I added values to the tables, made a query displaying tblConditional, added tblTense and made it display the Tense. I would expect that it shows the correct tenses corresponding to the TenseIDs in tblConditional's column "TenseAntecedent". This works. However, doing the same thing with tblAspect (in this case I established the relationship via SQL-code and not via the relationship design window) does not work. It seems that establishing the relationship via SQL simply doesn't work. Especially the second relationship between tblTense and tblConditional does not work either. But here the question would naturally be: What value should the field "Tense" show in a query? The one designated by the TenseID in "TenseAntecedent" or the one in "TenseConsequent"? Maybe this is the reason why it won't let me establish the connection in the relationship design window. Here are questions:

1) Why does establishing a relationship via SQL not work? Am I doing something wrong?

2) What's up now with the double relationship that I want? Is that principally possible?

3) If it is not, how can I get what I want? Do I need two identical tables, so that I can have two separate primary keys?

4) What about the solution suggested by UnklDonald: Adding a list box in a form with a (fixed?) list of entries that may be added to the record? Is there something wrong with this or does it work? I actually tried doing this before I came up with the weird double relationship. It didn't work. I could add the values to the value list (in the properties dialog of the list box), but in the form it would not show anything when I clicked on the list box. Since I am not familiar with Base, I just went on and tried something else. How can I do still this? Is there a good documentation about this?

Thank you guys for your input on this! Sorry for the long post!
Skrag

[UnklDonald418]

OK, I started a test-database to make stuff more clear.

It would help if you uploaded your test database so that everyone is working from the same starting point.

If you aren't familiar with the upload process see:
viewtopic.php?f=74&t=8289

[Sliderule]

So the code works in showing the correct foreign keys. Regrettably, it does not show the column name of the foreign key. If you have suggestions how to accomplish this, I would be glad.

Use the following Query instead:

Code: Select all

SELECT 
   T.TABLE_NAME, 
   T.TABLE_TYPE, 
   C.FK_NAME AS "FOREIGN KEY NAME", 
   C.PK_NAME, 
   C.PKTABLE_NAME, 
   C.PKCOLUMN_NAME, 
   C.FKTABLE_NAME, 
   C.FKCOLUMN_NAME, 
   C.KEY_SEQ 
FROM INFORMATION_SCHEMA.SYSTEM_CROSSREFERENCE AS C, 
     INFORMATION_SCHEMA.SYSTEM_TABLES AS T 
WHERE C.PKTABLE_SCHEM = 'PUBLIC' 
  AND C.FKTABLE_SCHEM = 'PUBLIC' 
  AND C.FKTABLE_NAME = T.TABLE_NAME 
ORDER BY T.TABLE_NAME, 
         C.KEY_SEQ

If you want to run the above Query using Base, and, you do NOT send it as a direct command, but, rather use the Base Parser, use the Query below ( removed table alias names and qualifier each column name ):

Code: Select all

SELECT 
   INFORMATION_SCHEMA.SYSTEM_TABLES.TABLE_NAME, 
   INFORMATION_SCHEMA.SYSTEM_TABLES.TABLE_TYPE, 
   INFORMATION_SCHEMA.SYSTEM_CROSSREFERENCE.FK_NAME AS "FOREIGN KEY NAME", 
   INFORMATION_SCHEMA.SYSTEM_CROSSREFERENCE.PK_NAME, 
   INFORMATION_SCHEMA.SYSTEM_CROSSREFERENCE.PKTABLE_NAME, 
   INFORMATION_SCHEMA.SYSTEM_CROSSREFERENCE.PKCOLUMN_NAME, 
   INFORMATION_SCHEMA.SYSTEM_CROSSREFERENCE.FKTABLE_NAME, 
   INFORMATION_SCHEMA.SYSTEM_CROSSREFERENCE.FKCOLUMN_NAME, 
   INFORMATION_SCHEMA.SYSTEM_CROSSREFERENCE.KEY_SEQ 
FROM INFORMATION_SCHEMA.SYSTEM_CROSSREFERENCE, 
     INFORMATION_SCHEMA.SYSTEM_TABLES 
WHERE INFORMATION_SCHEMA.SYSTEM_CROSSREFERENCE.PKTABLE_SCHEM = 'PUBLIC' 
  AND INFORMATION_SCHEMA.SYSTEM_CROSSREFERENCE.FKTABLE_SCHEM = 'PUBLIC' 
  AND INFORMATION_SCHEMA.SYSTEM_CROSSREFERENCE.FKTABLE_NAME = INFORMATION_SCHEMA.SYSTEM_TABLES.TABLE_NAME 
ORDER BY INFORMATION_SCHEMA.SYSTEM_TABLES.TABLE_NAME, 
         INFORMATION_SCHEMA.SYSTEM_CROSSREFERENCE.KEY_SEQ

Additional comment : if you have issued a DDL ( Data Definition Language ) statement, for example, CREATE CACHED TABLE or ALTER TABLE . . . it will NOT show up in the RELATIONSHIP display window, until EITHER :

1. You save your OpenOffice / LibreOffice Base program and restart it
2. From the Menu: View -> Refresh Tables

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[UnklDonald418]

Adding a list box in a form with a (fixed?) list of entries that may be added to the record? Is there something wrong with this or does it work?

You can safely use list boxes for tables with static data. For instance if tblAspect has a limited number of static Aspects then it is a candidate for a list box. They wouldn't work very well if there is a possibility of an Aspect being deleted.
I am put together an example demonstrating listboxes, where the table PLACES has six foreign keys all referencing the table COLORS. The form PLACES demonstrates how they work.

Demo04.odb

(12.55 KiB) Downloaded 214 times

[Arineckaig]

Adding a list box in a form with a (fixed?) list of entries that may be added to the record? Is there something wrong with this or does it work? I actually tried doing this before I came up with the weird double relationship. It didn't work. I could add the values to the value list (in the properties dialog of the list box), but in the form it would not show anything when I clicked on the list box.

A list box CAN be used with a fixed list of entries: in the form of a valuelist as opposed to referencing data from a different table. Base can be confusing as strangely enough the items to be displayed for selection from the listbox should be entered in the 'List entries' property box under the General tab of the listbox: the 'List content' property under Data tab can be left empty unless the values to be stored in the underlying table are required to differ from those as displayed for selection in the listbox. In either case the list of items is (or is not) entered in the relevant property with a shift-return between each item.

[Skrag]

Additional comment : if you have issued a DDL ( Data Definition Language ) statement, for example, CREATE CACHED TABLE or ALTER TABLE . . . it will NOT show up in the RELATIONSHIP display window, until EITHER :

1. You save your OpenOffice / LibreOffice Base program and restart it
2. From the Menu: View -> Refresh Tables

Sliderule

Thanks, I think this answers the question quite well! Thanks too for the code for retrieving the foreign keys!

You can safely use list boxes for tables with static data. For instance if tblAspect has a limited number of static Aspects then it is a candidate for a list box. They wouldn't work very well if there is a possibility of an Aspect being deleted.
I am put together an example demonstrating listboxes, where the table PLACES has six foreign keys all referencing the table COLORS. The form PLACES demonstrates how they work.

Thank you! I looked at your database, but in the relationship windows I didn't find any foreign keys. Does this work only through the SQL-Code in the listbox?

Code: Select all

SELECT "COLOR", "ID" FROM "COLORS"

A list box CAN be used with a fixed list of entries: in the form of a valuelist as opposed to referencing data from a different table. Base can be confusing as strangely enough the items to be displayed for selection from the listbox should be entered in the 'List entries' property box under the General tab of the listbox: the 'List content' property under Data tab can be left empty unless the values to be stored in the underlying table are required to differ from those as displayed for selection in the listbox. In either case the list of items is (or is not) entered in the relevant property with a shift-return between each item.

In my OpenOffice, there is no List entries property box unter the General tab. Instead, under the Data tab, I can choose the kind of List box as valuelist and then enter the content of the list. Do you now how to do this or can you give me the link to a documentation about this?

Thanks to all of you!
Skrag

[Arineckaig]

In my OpenOffice, there is no List entries property box unter the General tab. Instead, under the Data tab,

Very strange! Can offer no explanation as with the same version of AOO I get:

That item will understandably be greyed out as not relevant UNLESS "Valuelist" has been chosen as the "Type of list contents" under the Data tab.

[Skrag]

Oh yes, my bad! I didn't see it since it was greyed out! Thanks to all of you, I think my problem is solved!

[UnklDonald418]

in the relationship windows I didn't find any foreign keys. Does this work only through the SQL-Code in the listbox?

Yes, that is why lists should be static. Actually, adding records would work OK. Changes to your list would change all references which may or may not be a good thing. Deletions is where real problems could occur, because without a declared relationship there would be no warning messages or delete options.