LO 4.0: intrinsic database: relations only between primaries keys?

users
#1

Hello

(It seems that my message did not pass to the gmane newserver, that is
why I resend this message)

I am using Kubuntu 10.04 and installed the latest LO 4.0 (the problem
occurs also in 3.6, and in OO 3.3).

I set up the intrinsic (hsql) data base. I created two tables using the
templates: one is Task (from business) the other Accounts (from
personal). All the field values are the default ones. I use an existing
field as a primary key (autovalue on), namely TaskId (numeric) and
AccountId (numeric). I insert some data and save them.

I then set up a relation between the two tables, where I relate the
fields corresponding to the primary keys. (TaskId-->AccountId) I save
the relation and reopen it.

Now I want to edit the relation and I am offered the possibility to add
more fields. So I select the notes file in either table but I receive
the following error.

Primary or unique constraint required on main table.
"Accounts" in statement [ALTER TABLE "Tasks"
  ADD FOREIGN KEY ("TaskId","Notes") REFERENCE "Accounts"("AccountI","Notes")]

I am puzzled, in the intrinsic database only relations between primary
keys are possible?? Is this a property of the intrinsic database, or is it a
bug? I am thinking of submitting a bug report.

Any help would be appreciated

Uwe Brauer

#2

`

Hello

(It seems that my message did not pass to the gmane newserver, that is
why I resend this message)

I am using Kubuntu 10.04 and installed the latest LO 4.0 (the problem
occurs also in 3.6, and in OO 3.3).

I set up the intrinsic (hsql) data base. I created two tables using the
templates: one is Task (from business) the other Accounts (from
personal). All the field values are the default ones. I use an existing
field as a primary key (autovalue on), namely TaskId (numeric) and
AccountId (numeric). I insert some data and save them.

I then set up a relation between the two tables, where I relate the
fields corresponding to the primary keys. (TaskId-->AccountId) I save
the relation and reopen it.

Now I want to edit the relation and I am offered the possibility to add
more fields. So I select the notes file in either table but I receive
the following error.

  Primary or unique constraint required on main table.
  "Accounts" in statement [ALTER TABLE "Tasks"
   ADD FOREIGN KEY ("TaskId","Notes") REFERENCE "Accounts"("AccountI","Notes")]
  I am puzzled, in the intrinsic database only relations between primary
keys are possible?? Is this a property of the intrinsic database, or is it a
bug? I am thinking of submitting a bug report.

Any help would be appreciated

Uwe Brauer

      How do you define an "intrinsic database"? I do not understand the term.
      Primary-foreign key pairs are created when a table is normalized. (There several levels of normalized tables.) These pairs are based upon the relationships between the fields of the original table.
      So, you probably should put all the fields of the two tables into a single table with the exception of any fields that belong to both tables. For example the single table contains only one primary key ("ID") and one field named "Notes". Then look for any fields (other than "ID") that determine the values of other fields. (In other words, begin to normalize the table.) Without knowing all of the fields that you are using, it would be difficult to know what fields these might be.
      FYI, very seldom will two tables have a 1:1 relationship (linking the primary keys of the two tables). Very likely, yours doesn't either. Most often, these relationships involve a primary key of one table and a foreign key of the other.

--Dan

#3

Hi Uwe,

Now I want to edit the relation and I am offered the possibility to add
more fields. So I select the notes file in either table but I receive
the following error.

Primary or unique constraint required on main table.
"Accounts" in statement [ALTER TABLE "Tasks"
  ADD FOREIGN KEY ("TaskId","Notes") REFERENCE "Accounts"("AccountI","Notes")]

I am puzzled, in the intrinsic database only relations between primary
keys are possible?? Is this a property of the intrinsic database, or is it a
bug? I am thinking of submitting a bug report.

The following possibilities occur to me :

(a) you might need to drop your previously configured constraint before
you can set up a new one via the GUI. From what I recall, this might
only be possible manually, i.e. by issuing a SQL command (menu Tools >
SQL) to DROP the CONSTRAINT from the tables, then shutting down the db
file, closing LO completely and re-opening.

(b) you might not be able to set combined constraints on more than one
field in both tables simultaneously via the GUI - again you may find
that you have to do this manually (although it may actually turn out to
be a limitation of hsqldb 1.8, I haven't checked)

Alex

#4

"Dan" == Dan Lewis <elderdanlewis@gmail.com> writes:

   > `
   >>
   >> Uwe Brauer
   > How do you define an "intrinsic database"? I do not understand
   > the term.

Well in my understanding there are two possibilities,

    - either Base can connect to an external database such as mysql via
       a number of drivers or

    - it uses a database it ships (I think it is based in
       hsql). Because of a lack of a word I denoted this as intrinsic.

   > Primary-foreign key pairs are created when a table is
   > normalized. (There several levels of normalized tables.) These pairs
   > are based upon the relationships between the fields of the original
   > table.
   > So, you probably should put all the fields of the two tables into
   > a single table with the exception of any fields that belong to both
   > tables. For example the single table contains only one primary key
   > ("ID") and one field named "Notes". Then look for any fields (other
   > than "ID") that determine the values of other fields. (In other words,
   > begin to normalize the table.) Without knowing all of the fields that
   > you are using, it would be difficult to know what fields these might
   > be.
   > FYI, very seldom will two tables have a 1:1 relationship (linking
   > the primary keys of the two tables). Very likely, yours doesn't
   > either. Most often, these relationships involve a primary key of one
   > table and a foreign key of the other.

I am not entirely sure I understand: I set up a new base, no the primary
keys are not any existing field of the table (autovalue is On)

My tables are the following (they are just toy models in order to
understand the relation ship concept)

Task:
Name Type
Id (generated by OO as a primary key)
Task Id Integer
Description Memo Longchar
Notes Memo Longchar

Authors
Name Type
Id (generated by OO as a primary key)
Author Id Integer
FirstName Memo Longchar
Nationality Notes Memo Longchar

Now I try to make a relation between the two tables, and in the GUI I
select the field Task Id and Author id in the other table, since both
fields have the same type.

I receive the following error:

SQL Status: 23000
Error code: -177

Integrity constraint violation - no parent 1, table: Authors in
statement [ALTER TABLE "Authors" ADD FOREIGN KEY ("AuthorID")
REFERENCES "Tasks" ("ID")]

Now the gui allow me to add more fields so I add both primary keys, gain
an error:
SQL Status: S0011
Error code: -170

Primary or unique constraint required on main table: "Tasks" in
statement [ALTER TABLE "Authors" ADD FOREIGN KEY ("AuthorID","ID")
REFERENCES "Tasks" ("TaskID","ID")]

The only one which works, using the GUI is between the primaries keys!!!

Did you try it out your self and you are able to set more relations
between the tables?

As I said, in another mail, when I connect the data base to mysql via
ODBC then I can set more relations, but they are not saved..

Uwe

#5

"Alexander" == Alexander Thurgood <alex.thurgood@gmail.com> writes:

   > Hi Uwe,

   >> Now I want to edit the relation and I am offered the possibility to add
   >> more fields. So I select the notes file in either table but I receive
   >> the following error.
   >>
   >> Primary or unique constraint required on main table. "Accounts"
   >> in statement [ALTER TABLE "Tasks" ADD FOREIGN KEY
   >> ("TaskId","Notes") REFERENCE "Accounts"("AccountI","Notes")]
   >>
   >> I am puzzled, in the intrinsic database only relations between
   >> primary keys are possible?? Is this a property of the intrinsic
   >> database, or is it a bug? I am thinking of submitting a bug
   >> report.
   >>

   > The following possibilities occur to me :

   > (a) you might need to drop your previously configured constraint
   > before you can set up a new one via the GUI. From what I recall,
   > this might only be possible manually, i.e. by issuing a SQL command
   > (menu Tools > SQL) to DROP the CONSTRAINT from the tables, then
   > shutting down the db file, closing LO completely and re-opening.

   > (b) you might not be able to set combined constraints on more than one
   > field in both tables simultaneously via the GUI - again you may
   > find that you have to do this manually (although it may actually
   > turn out to be a limitation of hsqldb 1.8, I haven't checked)

This is my impression. Even when I generate for the first time a
relation ship via the GUI, I can only have the primary keys, if I put
more field in the relation it fails.

And if I just set it up for the primary keys and later edit, same
result.

I think it is time for a bug report.

Uwe

#6

Perhaps you need to learn more about primary keys, foreign keys, and the relationship that must exist between them. Another topic that would help is normalizing tables. Still another topic is constraints that must be applied to either type of keys. (This one really goes to the error message you got. (I make these suggestions because it is what I had to do to understand tables in a relational database.)
      I am a little curious. Your email address implies you may live in Spain. Your name looks German to me. If you are German, then there is a Base Handbook written in German that you might be able to use. Tom might be able to tell you from where to download it on the LibreOffice wiki if that would help.
      As far as MySQL problems, I think I tried to modify fields in a table while using MySQL. They were not saved either.

--Dan

#7

Hi Uwe,

As I said, in another mail, when I connect the data base to mysql via
ODBC then I can set more relations, but they are not saved..

I take it that you are using InnoDB engine in your mysql db ?

Otherwise, e.g. if you are using myisam or heap as the default engine,
relations are not supported by mysql, i.e. they are not enforced
(although they can be stored as a textual reference).

This is true whether you are using ODBC, JDBC or the native mysql
connector to reach your tables from within LO.

Alex

#8

"Dan" == Dan Lewis <elderdanlewis@gmail.com> writes:

   > Perhaps you need to learn more about primary keys, foreign keys,
   > and the relationship that must exist between them. Another topic that
   > would help is normalizing tables. Still another topic is constraints
   > that must be applied to either type of keys. (This one really goes to
   > the error message you got. (I make these suggestions because it is
   > what I had to do to understand tables in a relational database.)
Yes of course, I desperately need to learn more. But the issue is that
the GUI seems only to set and save relations between primaries keys. Did
you try it out via the GUI, what is your experience?

   > I am a little curious. Your email address implies you may live in
   > Spain.

Yes, I do.

   > Your name looks German to me. If you are German, then there is
   > a Base Handbook written in German that you might be able to use.

Yes again, I am German.
   > Tom might be able to tell you from where to download it on the
   > LibreOffice wiki if that would help.
That would be extremely helpful. I googled around of course but mostly
found old material (2007/08).

   > As far as MySQL problems, I think I tried to modify fields in a
   > table while using MySQL. They were not saved either.

Aha so there is a problem?!

Uwe

#9

Hi Uwe,

Well show engines;
showed

InnoDB | YES | Supports transactions | YES | YES | YES

So I presume the answer is yes.

In theory, yes, but you might want to check each table to see whether it
does actually use the InnoDB engine and has not defaulted to myisam.

   > This is true whether you are using ODBC, JDBC or the native mysql
   > connector to reach your tables from within LO.
A question, can you set various relations between tables, using mysql
and they are saved?

Yes, but I don't use LO for this, I either code it by hand from the
mysql command line interface or when I'm feeling lazy use phpmysqladmin
or MySQL Workbench.

The reason : LO, and previously OOo, did not always make a very good job
of defining my table fields the way I wanted them.

Alex

Alex

#10

Hi Uwe,

Robert Grosskopf wrote a handbook in German that should be available on
the German LO wiki somewhere.

   > As far as MySQL problems, I think I tried to modify fields in a
   > table while using MySQL. They were not saved either.

You might be right, I haven't tried this recently. I gave up using LO or
even OOo to define my fields in mysql because it used to mess them up
somewhat, so I took to doing things via the command line interface. It
was hard going (and sometimes still is with mysql's rather unhelpful
obscure error messages), but at least I learnt more that way.

Alex

#11

Hi :slight_smile:
Robert Großkopf on the Docs Team's amiling list gave me this link 
http://de.libreoffice.org/hilfe-kontakt/handbuecher/

Hope it helps!
Good luck and happy hunting :wink:
Regards from
Tom :slight_smile:

#12

At the time I thought there was a problem, but after writing about MySQL, I tried again. This time I was able to modify a field and later when editing that table, I saw that the changes had been saved.
      If you saw this link: http://de.libreoffice.org/hilfe-kontakt/handbuecher/, this should explain what you need to know in a language that you better understand than English.

--Dan

#13

Some basics about creating a database:
1) You begin with data that you want to use for some purpose or purposes.
2) Then you design the database to organize this data so that it will be useful.

For example: an address database:
      In the beginning, the database consists of information such as names, addresses, email address, phone numbers, etc.
      The first thing you would want to do is to organize this data so that a name is linked to its address, its email address, its phone numbers, etc. Now you have rows of data, each one of them containing information about a single person (a relationship exists between the data for each row). Each of these pieces of information is a field.
      If you look at these rows, you will see that they also contain some things in common. Several rows can contain data about your friends, others are about your relatives, and others are about companies you do business with. You probably know of other possibilities. So, these rows can be organized into groups based upon what they have in common. (This too is a relationship.)
      The next thing you should do is to further organize these rows in each one of these groups to make them more useful. You could form a table using these as the column headings: name, address, email address, and phone number. ( Now you have a table for each group of rows. So, if you want data about a particular contact, you can go to the table that contains it. If you want to look at the data for a relative, you go to the table containing all of your relatives.
      This is basically how a flat database is created. Relational databases begin in the same way.
      At this point, each of these tables are checked to see whether they are "normal" or not. For example, a contact is likely to have multiple phone numbers or other possible multiple entries. Each of these have something in common (another relationship). We can remove the field containing multiple entries forming a new table. To keep the relationship between this new table with the original table, we create a primary-foreign key pair (the primary key is for the new table, and the foreign key is added to the original table in place of the fields we removed.) This is what is done to make a table "first normal form".
      There are several levels of a table being normal: "first normal form" to "fourth normal form" and beyond. "Fourth normal form" is considered to be the standard against which a table should be judged.
      The point being that we do not create tables with their fields and then define the relationship between them. We begin the the fields that we know we will need, combine the fields based upon relationships between data, and combine the fields into tables based upon relationships between the fields. The the tables are normalized up to "fourth normal form" creating new tables and modifying the old ones. Primary-foreign key pairs are used to define the relationship of the new tables and the modified old table from which the new table came.
      Hopefully this will help some.

--Dan

#14

Dan,
This is good! It is a good start on an introduction to databases for newbies.
It should be put in the Base manual?
Girvin Herr

Dan Lewis wrote:
<snip>

#15

Have you read Chapter 2 of the Base Guide? (Planning/Designing your database) There are 56 pages in which I used this approach for all the parts of a database. There are many questions in it designed to help a person.

--Dan

https://wiki.documentfoundation.org/Documentation/Publications

#16

Dan,
Chapter 2?? All I have is Chapter 1 - "Introducing Base" and Chapter 8 - "Getting Started with Base". I see there is a draft copy of Chapter 2 and 3 now. I will read them.
Thanks.
Girvin Herr

Dan Lewis wrote: