Table relationships feature not working

[matthewcl375]

Base is great but recently i have tried using relationships and they do not work.
I can design the relationships but then i press save and i close the relationships designer. When i go back into the designer my relationships have been deleted!

Can anyone help with this?

[r4zoli]

What OS which OOo version?

Base file with embedded HSQLDB database or connected to external source?

Fileds which you tried to establish relations have same type?

All tables have primary keys?

[matthewcl375]

I am using Windows Vista Home Premium with OOo 2.4.0 .
The database is a base file with an embedded HSQL database.
All my tables have primary keys.

[scanrik]

I'm having the same problem (relationships lost on closing "Relation design").
Running OOo 2.4.1 on Win/XP Pro/SP2

[scanrik]

I have now succeeded to establish persistent relationships between tables.
(I learned how by recreating d/b in Microsoft Access, where the context-sensitive help is much better!)

At least one side of every relationship must be a UNIQUE key (a.k.a., Primary Key).
It is not enough that the fields on either side have unique (no duplicates) entries!
(In terms of d/b logic, I cannot see why this would not work. But that's an implementation issue.)

Once this condition is met, the relationships will be retained upon closing the "Relation design" window.

Suggestions to developers:
1) Tell user of error. Don't just drop the links silently.
2) Improve documentation, clarifying this requirement.

[TheGurkha]

Glad you got it fixed.

If your question has been answered please go to your first post, use the edit button and add [Solved] as the first word of the title. You can also select the green tick icon.

If you your suggestions to be seen by the developers (only users hang out here) you can do so by following:
[Tutorial]Reporting bugs or suggestions.

[Zeedok]

I have also run into this problem - at least I think it is the same problem. I can successfully create single table dbs with base (using 2.4.1 on Ubuntu 8.04 and WinVista, BTW), but cannot get the relationships to work.

Essentially I am starting with two tables (once I get this working, I will increase the complexity) and trying to relate them with one common field (which is the primary key for my 'main' table). I have entered that field as a foreign key into my second table (which has it's own primary key). On Ubuntu I encountered a number of problems - eg sometimes the relation would be deleted (even though I had saved it), or the relation would remain but "Update cascade" would be changed to "Do nothing" (see screenshots). On Vista, the "Update cascade" box remains checked intermittently, but the fields are not updated.

Am I doing something stupid? What does "Update cascade" do - I have obviously misunderstood it's purpose? This should be easier to sort out, shouldn't it?

PS In my screen shots - I am entering patient demographic data with a "PatientID" which will be unique for each patient, then collecting history (hence the History table) on more than one occasion (hence the primary key "HistoryID"). Each patient will have more than one "History" assessment.

[NoelM]

I have the same problem.
I am using OpenOffice v3.0 on Windows XP.
I have 3 tables A, B, and C. A has primary key of field D, B has primary key of fields E and F, and C has primary key of fields G H and I.
Fields D, E and G have the same data, and F and H have the same data.
I can crate the relationship between A and B as it is based on a single field, when I try the same between B and C it disappears after saving, closing the window, then opening it again, even though I use the full primary key from B as they relate B to C.

[r4zoli]

If you have a odb file with bug please upload here or attach to the issue 95185.

[marccantwell]

I am having the same issues. I am using the off the shelf templates to test BASE out and am having no luck. The relationships won't stick. After they are created and saved, window is closed, and reopened, they disappear. As a results forms and queries are useless. Kinda a big deal don't you think, if your relations don't work in a relational database?

Any fixes in sight?

[vstarc16]

I am having the same problem. Due to data losses I have migrated HSQL database (OO 2.4.1) to MySQL 4 months ago, but was disappointed that I could not use relations any more. Relations are properly designed via MySQ Query Browser, but I can not see them in Base, nor I could design new relation. And the same continues with OO 3.0.

My system is WinXP, and OO 3.0
Vanja

[Villeroy]

Base is mainly a frontend. Data definition works more or less with it's native HSQLDB and dBase, but you must not use the data definition features for any other type of database.
Data manipulation works as expected since there is no tool other than the command line (menu:Tools>SQL).
Very simple SELECTs work with Base, but you are better off with the command line called "direct SQL mode".
The forms are pretty awkward collections of controls within forms and linked subforms, embedded in Writer documents. If you understand all the undocumented features as well as the missing and broken features you may work out something useful. Since only a few dozend people world-wide can master this collection of bugs and restrictions there are only trivial example databases. Everything is undocumented. I can not tell much about reports but the Writer tools are the same. The 5th bug-release of the report builder may work now or not. I don't care anymore.
They effectively dropped the ability to extend HSQL with userdefined Java functions. Embedded macros could be useful to develop some helper routines for better forms, but they postponed the required framework. Additionally they destroyed support of Python macros and add-ons based on the Python language.
The only use case where Base does a reasonably good job is the import of a wide variety of data, filter them by means of simple SQL and use them as registered sources in Writer and Calc.

[vstarc16]

In the mean time I have found that relations between MySQL tables are functional, although there is no graphic representation.

Now I have been using Base for almost 3 years and I agree with Villeroy statements. There has been lot of time spent uselessly trying to find out what is wrong in my work, to eventually find out that problem is in Base.
I am MD running small ED, and I have switched to Base because we needed informatization, my chief were not willing to pay for licence software (and they still aren't), and I did not want to use illegal Access. Now we have MySQL database with Base as front end, but I wonder is there better free software that could be used as front end with capabilities of Forms and Reports. Any suggestions?

Vanja

[r4zoli]

If you want to change relations use MySQL tools, and you can use existing relations (not showed in OOo) on forms and queries, and queries in reports.

[DrewJensen]

Oh, come on now...you know that the old adage is true

You get what you pay for!

[vstarc16]

Dear r4zoli thanks for sugestion, I have been using MySQL Query Browser from the beginning for such purposes. What made me write previous post was fact that after switching to OO 3.0 I had to rewrite all reports (because some aggregate date were not showing) and that relations were not showing so my first thought were that I can not use relational structure anymore what luckily proved wrong.
I agree with Drew. Considering that OO is free I am more than satisfied with it's capabilities, especially those of Writer,Calc and Impress in which I do not lack any important feature, nor have any objections to their functioning. Even though I had problems with Base I managed to build (especially after separating data keeping and structure to MySQL) very useful database that is run 24 h a day that significantly improved medical data handling (for billing we have professional software).

Vanja

[DrewJensen]

- i was trying to pick a fight this morning..and there you go being all nice and cordial and civilized...

[Villeroy]

Get what we pay for? Come on, we get even less than zero!
Need a reliable and performant database? Yes, of course but not in the Base container! You may lose all data and not the current record.
Data manipulation queries? Use the command line!
You are shure that your SELECT should work, but it doesn't? Use the command line (direct SQL mode)!
Desktop links to forms and reports? Create them outside the Base container!
Quick and easy pivot tables and charts? Create them outside the Base container!
Macros? Yes, of course but not in the Base container!
Want simple form-subform relations between different data sources? It's possible outside the Base container!
Want to use your database with any tool? Don't use the self-contained HSQL in the Base container!

The whole thing is broken by design. The uneducated crowd shouted for a database in a single file, something like Access. OK, now we have what we shouted for.

[MarkYoung]

I have the same problem.
I am using OpenOffice v3.0 on Windows XP.
I have 3 tables A, B, and C. A has primary key of field D, B has primary key of fields E and F, and C has primary key of fields G H and I.
Fields D, E and G have the same data, and F and H have the same data.
I can crate the relationship between A and B as it is based on a single field, when I try the same between B and C it disappears after saving, closing the window, then opening it again, even though I use the full primary key from B as they relate B to C.

I dunno if anyone's still following after all these months, but...

I am using 3.0.1 on Vista and I had a similar problem: when the foreign key is composite, creating a relationship thru the GUI doesn't work (or doesn't seem to work -- the relationship is not re-displayed when the relationships window is re-opened, and it does not complain that the relationship already exists when I try again to create it).

What I found, tho, was that the SQL tool could be used to create the relationship. For your example, the SQL command would be:

ALTER TABLE C
ADD CONSTRAINT C_B_FK
FOREIGN KEY(G, H)
REFERENCES B(E, F)

(You can also add ON UPDATE CASCADE and ON UPDATE DELETE as desired.) The relationship is created and displays properly when the Relationships window is re-opened. Once the relationship is there, you can use the GUI to change its update & delete properties.

But apparently you can't delete the relationship using the GUI. It erases the lines, and they are still not there when I re-open the relationships window, but when I try to create the relationship again (using drag-and-drop) it complains that the relationship already exists. Clicking the Edit button shows me the relationship as it was. (I expect I'll need to do an alter table drop constraint to get rid of it.)

(Note on the SQL command above -- the fields in the REFERENCES line have to be in the same order as in the table itself -- REFERENCES B(F, E) would not work, even if G and H were similarly swapped in the previous line. I dunno if that's standard behaviour....)

I've attached a simple school database -- courses and prerequisites. The PREREQUISITE table is a linking table for a recursive relationship on COURSES. Two relationships were added to PREREQUISITE using the SQL tool, and the second relationship was deleted in the GUI. The relationship window shows the first relationship still there, but not the second. But as you can see if you drag from REQD to DEPT, the second relationship is still present.

SQL-Table-Creation.odb

Table with composite foreign keys

(13.54 KiB) Downloaded 412 times

[Villeroy]

It's a well known issue that data definition queries (relations and table design) are not fully supported in Base, not even for it's so called "integrated HSQLDB". If it works through the "command line" Tools>SQL... feel happy. You do understand what you want to do but the GUI can't quite follow. Next version will come with the best Base we've ever seen.

[chrismac]

A very helpful tool for creating & managing relationships with MySQL tables is at the below URL :

http://dev.mysql.com/doc/workbench/en/index.html

Something to know about this is that, when 'synchronizing' the database/tables OOo loses sight of the Primary Keys for those tables
and they need to be recreated in OOo if you want to edit the tables again - even though Workbench and MySQL believe the PK's to be OK.

Chris

[phil_oo_user]

Multiple relationships - instead of creating by dragging in the gui, go to the relationships window in the tools menu and having opened the relationships window you will find that the insert menu has `new relationships' in the dropdown. I found that relationships designed here persisted after closing the window.

[db_geek]

I have the same problem with creating relations with tables in OO Base 3.2 using ubuntu 10.04 . I can create the one to many links and as long as I don't try to edit the properties of the links 9 ie set cascade updates and deletes etc) it will save the link. For read only this seems to work, however no updating can be done. If I try to edit the properties when the link between the tables is created it allows me to select the fields and select the update and delete options for cascading, however it will not accept the final configuration, once the properties window has been changed in any way. Clicking the close window X for that pop up window simply deletes the relation link. Any attempts to recreate the link do not show the link to allow edits. To clear and allow recreation, all tables have to be deleted from the relationship window, then saved and db closed and reopened. Then re-add tables create the links and again when one tries to edit the properties it is not allowed.

It appears that as long as there are no relationships defined, no predefined links will show up in the add subform portion of the form wizard either. I may be in error on this assumption, as I was not able to create and edit the relationship to confirm the assumption.

Is there a patch and update or a workaround anybody is aware of???