Relationships in Base

users
#1

I have just started using LO Base 3.4.4. I created 2 tables with a
relationship between them. I then deleted one of the tables and created
another one with a relationship to the first. When I try to add records to
the table is gives me the following error message: "SQL Status: 23000
Error code: -177

Integrity constraint violation - no parent SYS_FK_110 table: Patient Old in
statement [INSERT INTO "Pharm Rcpt" (
"pr_Cost","pr_Date","pr_Doctor","pr_Med","pr_Patient","pr_Pharm","pr_Qty")
VALUES ( ?,?,?,?,?,?,?)]"

It looks like the relationship did not get deleted when the table was
deleted.

#2

I just created another form and when I try to enter data I get the following
error message: "Integrity constraint violation - no parent SYS_FK_81 table"

Does anyone know where these SYS*** tables are and how they can be
deleted???

Thanks

#3

When you have the database opened, use
       Tools > Relationships
to define relationships between tables or to delete relationships.
     What I would like to know is the specific steps you used to create
the relationship between the two tables? How did you add the second
relationship you mentioned?

--Dan

#4

Am 27.02.2012 11:36, gerbreown wrote:

I have just started using LO Base 3.4.4. I created 2 tables with a
relationship between them. I then deleted one of the tables and created
another one with a relationship to the first. When I try to add records to
the table is gives me the following error message: "SQL Status: 23000
Error code: -177

Integrity constraint violation - no parent SYS_FK_110 table: Patient Old in
statement [INSERT INTO "Pharm Rcpt" (
"pr_Cost","pr_Date","pr_Doctor","pr_Med","pr_Patient","pr_Pharm","pr_Qty")
VALUES ( ?,?,?,?,?,?,?)]"

It looks like the relationship did not get deleted when the table was
deleted.

--
View this message in context: http://nabble.documentfoundation.org/Relationships-in-Base-tp3780236p3780236.html
Sent from the Users mailing list archive at Nabble.com.

You never define a relationship in Base. You always do that in a database software even when you are working with the built-in database engine (status bar reads: "embedded HSQLDB"), this "integrated" database behaves exactly like any external database such as MySQL and many others.
The self explaining error message comes from that database software and it says that you try to enter a patient-ID that has no counterpart in the patient table's primary key.

Base can help you to avoid all integrity constraint errors by means of subforms and list boxes which are the 2 key elements to edit relational data in Base forms.

[Example] Relations reflected by list boxes in forms:
http://user.services.openoffice.org/en/forum/viewtopic.php?f=100&t=40444

#5

I used the Tools > Relationships and dragged/drug the field in one table to
the matching field in the other table, just like in MS Access.

The second table was done the same way.

I now have a lot of different tables with relationships between them. When
I create the forms for data entry to these various tables it causes the same
error because of the deleted table. I had created another table and set up
the relationships for it and when I kept getting the error, I renamed the
new table to the old table that had been deleted but it still didn't work.

The problem seems to be with the SYS_FK_xxx tables that didn't get deleted
when I deleted the table. Do you know where these tables are located and
how they can be manually deleted/edited?

Thanks

#6

It looks like that when I renamed my new table to the old table the data got
dropped. I am using combo-boxes and they showed the data so don't know why
I was able to see the data. Maybe it was just the name and not the PK that
was left in the table. But after reading your reply I looked at the table
and it was empty.

I dropped the new table and then recreated it and entered two records and
now my form are working as they should.

I find that when having problems just being able to talk about them they get
solved by getting another perspective on them.

Thanks for your help.

#7

Yes, I know where the table is located. First step is to make a
copy of your database. Then rename it so that its extension is changed
from x.odb to x.zip. Open x.zip with an archive manager (Winzip or 7zip
are two that will do this). This will produce several folders and three
files. Double click the database folder to open it. Then open the script
file. The relationship can be deleted there. After making your changes,
you zip all the files together and rename the database file from x.zip
to x.odb.
     WARNING!! Unless you are well versed in extracting files from a
zipped format or adding them to it, this is not something that you
should be doing.
     Here is a work around to try. Open the database, x.odb. Then from
its main database window, create a new database, y.odb. Copy and paste
the tables from x.odb to y.odb. Define the relationships that you want,
and recreate your forms.
     Suggestion: Download Getting Started with Base (one of the chapters
in the Getting Started Guide). It might be a refresher on the
differences between Access and Base (there are some differences). Also,
please study the section on how to set and modify relationships using
Base.

--Dan

#8

See my previous post on how I solved the problem.

You suggested that I view Chpt 8 in the Getting Started Guide. Have done
that.
Question: Why is there NOT a separate guide for Base like there is for the
others, ie Writer,Calc,etc?
Question2: What are the parts of LO, ie Writer,Base,Calc called?
Applications, modules or what?

Thanks

#9

Hi :slight_smile:
The separate Base Guide is being worked on but there is only about 1 person
working on it with occasional help from 1 other person who does the
proof-reading. You can see some of the draft chaters at
http://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Base_Guide

Sorry this is still not particularly help
Apols and regards from
Tom :slight_smile:

#10

Tue, 2012-02-28 at 02:44 -0800, gerbreown wrote:

See my previous post on how I solved the problem.

You suggested that I view Chpt 8 in the Getting Started Guide. Have done
that.
Question: Why is there NOT a separate guide for Base like there is for the
others, ie Writer,Calc,etc?
Question2: What are the parts of LO, ie Writer,Base,Calc called?
Applications, modules or what?

Thanks

     1) Very few people in the world are willing to take the time to
write the Base Guide. There are many more people who have worked on the
other user guides.
     2) I'm not sure that there is a consensus for the answer to this
question. When speaking of the parts of LO software, modules have been
used. But when speaking of the the functions of the software, components
have been use. For example, Writer is the word processing component.
Perhaps someone else knows the answer to this one.

--Dan

#11

Hi :slight_smile:
+1
I tend to call them apps most of the time but modules when i am being pedantic.  When talking to Windows users i sometimes call them programs as tho they were independent of each other.  It depends on how computer-savvy the person i'm talking to is. 
Regards from
Tom :slight_smile: