[Solved] Problem Creating Relationships

[rhammer54]

I am having problems with creating joins using the Relationship window where it is not allowing me to join the primary key in one table to the foreign key in the other.
The table with the foriegn key has been set to Integer. The table with the primary key to Integer Auto Increment. There are no duplicate entries in the table with primary key. And in the table with the foreign key all of the fields are referencing valid entries in the table with the primary key.

I don't get any error messages. But the tables do not get to be joined either by trying with the mouse or the pop up window?
Other tables join and I can create forms with data from joined one tables so its not a base configuation issue.
The two tables are accessed without problems using SQL embedded in PHP or at least no problem are reported.

I am using OpenOffice 4.1.5. My Database is MySQL on my 'localhost' with MySQL's own ODBC 8.0 Ansi Connector - this setup is working fine with alll my other tables.

What else can be wrong with my tables that Base is having a problem with?

Bob

[Villeroy]

Use the right database development tool for your MySQL backend. All tables, indices, relations, triggers should be readily done when connecting a Base document to your database.
There are many reasons why the creation of relations may fail, most prominently values on the n-side which do not exist on the 1-side.

[UnklDonald418]

Instead of using the crippled GUI, try establishing the relationship using the menu Tools>SQL
Something like

Code: Select all

ALTER TABLE "YourTableName" ADD CONSTRAINT "FK_SOMENAME" FOREIGN KEY ("ForeignKeyFieldName") 
REFERENCES "OtherTableName" ("PrimaryKeyName")

Be sure to inform the Base front end of the changes using the menu selection View>Refresh Tables
By using a named Constraint it is easy to reverse the changes if needed

Code: Select all

ALTER TABLE "YourTableName" DROP CONSTRAINT "FK_SOMENAME" 

You may not need the quotation marks when using MYSQL

[rhammer54]

Thanks for the replies

Replying to Villeroy. All of the values used as foreign keys appear as primary keys on the 1-side. I was wondering though what other problems in the database could cause this problem with Base.In my case the foreign key is in a table of type MyISAM wheras the primary key is in a table of type InnoDB - the types chosen were appropriate at the time they were designed - some years back - could this be the reason Base is complaining?

Replying to UnklDonald. Your SQL has worked and created the the join between my foreign key and primary key. I am able to produce forms in Base with the foreign key replaced by the fields from the associated table. Thank you for this. It took a bit of reseaching to get the SQL correct. MySQL in my case uses MariaDB which results in the SQL having no quotation marks whatsoever. Shame Base could make the joins?

Only other concern now is UnklDonald's comment 'Instead of using the crippled GUI' - question is Base not a good platform for interfacing to an external database? I am beginning to wonder.

thanks again Bob

[UnklDonald418]

In 2008 Base was state of the art in database development tools, and it remains a good tool for some applications. But in the intervening dozen years the state of the art has advanced while Base has not. There are still bug reports from 2008 that have never been assigned to a developer. The Apache Foundation does continue to provide a few updates on some of the other modules, but none relating to Base.
The LO branch of Open Office has made some changes to Base but most of their efforts are related to switching the back-end to Firebird. I suppose because unlike HSQL, Firebird doesn't depend on JAVA and the JRE.

[Villeroy]

Only other concern now is UnklDonald's comment 'Instead of using the crippled GUI' - question is Base not a good platform for interfacing to an external database? I am beginning to wonder.

It is a fairly good platform for interfacing to an external database if you have one. You can INSERT, EDIT, DELETE and SELECT data by means of forms. You can dump selected data into office documents. To CREATE, ALTER, DROP any database objects Base provides a command line only.