Re: [libreoffice-users] Re: LO 4.0: intrinsic database: relations only between primaries keys?

On 02/11/2013 05:31 PM, Uwe Brauer wrote:
> > > "Dan" == Dan Lewis <elderdanlewis@gmail.com> writes:
>     > `
>     > On 02/11/2013 09:18 AM, Uwe Brauer wrote:
>     >>
>     >> 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
     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

--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted