[Solved] Cannot save several relationships between 2 tables
[Solved] Cannot save several relationships between 2 tables
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
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
Last edited by Skrag on Fri Mar 10, 2017 2:49 pm, edited 2 times in total.
using OpenOffice 4.1.3 on macOS Sierra (10.12.3)
-
- Volunteer
- Posts: 1546
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Cannot save several relationships between two tables
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.
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.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Cannot save several relationships between two tables
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.
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.
using OpenOffice 4.1.3 on macOS Sierra (10.12.3)
-
- Volunteer
- Posts: 828
- Joined: Fri Nov 30, 2007 10:58 am
- Location: Scotland
Re: Cannot save several relationships between two tables
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.
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.
Last edited by Arineckaig on Wed Mar 01, 2017 9:54 pm, edited 1 time in total.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Re: Cannot save several relationships between two tables
Thank you, that seems to make sense. So two question remain for me:Arineckaig wrote: 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.
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?
using OpenOffice 4.1.3 on macOS Sierra (10.12.3)
-
- Volunteer
- Posts: 1546
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Cannot save several relationships between two tables
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:
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.
Code: Select all
ALTER TABLE "?" ADD CONSTRAINT FK_TLB_OMOD FOREIGN KEY ("OvertModalConsequentID") REFERENCES "tblOvertModal" ("OvertModalID");
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
-
- Volunteer
- Posts: 1546
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Cannot save several relationships between two tables
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.
You might be able to use list boxes on a form to select and display the values in those tables.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
-
- Volunteer
- Posts: 828
- Joined: Fri Nov 30, 2007 10:58 am
- Location: Scotland
Re: Cannot save several relationships between two tables
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.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Re: Cannot save several relationships between two tables
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.)
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.)
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
Re: Cannot save several relationships between two tables
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.keme wrote: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 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.
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:UnklDonald418 wrote: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: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.Code: Select all
ALTER TABLE "?" ADD CONSTRAINT FK_TLB_OMOD FOREIGN KEY ("OvertModalConsequentID") REFERENCES "tblOvertModal" ("OvertModalID");
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
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")
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")
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
using OpenOffice 4.1.3 on macOS Sierra (10.12.3)
-
- Volunteer
- Posts: 1546
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Cannot save several relationships between two tables
It would help if you uploaded your test database so that everyone is working from the same starting point.OK, I started a test-database to make stuff more clear.
If you aren't familiar with the upload process see:
viewtopic.php?f=74&t=8289
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Cannot save several relationships between two tables
Use the following Query instead:Skrag wrote: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.
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
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
- You save your OpenOffice / LibreOffice Base program and restart it
- From the Menu: View -> Refresh Tables
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
-
- Volunteer
- Posts: 1546
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Cannot save several relationships between two tables
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.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 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.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
-
- Volunteer
- Posts: 828
- Joined: Fri Nov 30, 2007 10:58 am
- Location: Scotland
Re: Cannot save several relationships between two tables
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.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.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Re: Cannot save several relationships between two tables
Thanks, I think this answers the question quite well! Thanks too for the code for retrieving the foreign keys!Sliderule wrote: 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 :
Sliderule
- You save your OpenOffice / LibreOffice Base program and restart it
- From the Menu: View -> Refresh Tables
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?UnklDonald418 wrote: 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.
Code: Select all
SELECT "COLOR", "ID" FROM "COLORS"
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?Arineckaig wrote: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.
Thanks to all of you!
Skrag
using OpenOffice 4.1.3 on macOS Sierra (10.12.3)
-
- Volunteer
- Posts: 828
- Joined: Fri Nov 30, 2007 10:58 am
- Location: Scotland
Re: Cannot save several relationships between two tables
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.In my OpenOffice, there is no List entries property box unter the General tab. Instead, under the Data tab,
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Re: Cannot save several relationships between two tables
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!
using OpenOffice 4.1.3 on macOS Sierra (10.12.3)
-
- Volunteer
- Posts: 1546
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: [SOLVED]Cannot save several relationships between two ta
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.in the relationship windows I didn't find any foreign keys. Does this work only through the SQL-Code in the listbox?
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11