Ask Your Question
0

integrity constraint violation

asked 2016-08-16 19:26:11 +0200

guanolto gravatar image

I am creating a form with subforms, as part of a database to log my work time and projects. I have LibreOffice v5.1.4.2 for Ubuntu Linux. Right now I'm testing data entry before adding the rest of the current subform and the three other subforms that I want to add. But Base refuses to let me add the record due to "integrity constraint violation - no parent SYS_FK_247" So far these error messages have clued me in to things I need to fix in my database, but this time I have no idea what to do. The Base instructions mention this error on pp49-50, but I can't figure out how those instructions relate to what I'm doing. I've looked online and found nothing relevant.

I have good screenshots to show what's happening but this website won't let me upload them. That's frustrating.

Since the error message mentions a "parent" then perhaps it has something to do with table relationships. Perhaps I have not specified properly which table is the parent, but I've looked countless times and cannot figure out how to specify that, both when creating the relationship and afterward. Also, the Table Relationships screen shows a many-to-one relationship. I would think that a one-to-one relationship would be more appropriate, since I intend to use the date as the one unique item linking all the tables and forms to each other. But again, I haven't figured out or found out yet how to specify that. The instructions are silent on that issue.

Perhaps I am just not understanding yet how to set up a form with subforms. The way I am now setting it up, I will have to enter the date at the beginning of the form, and also on each subform that I will use on a given day. Maybe it's silly to have to enter the date more than once on the same screen, but I haven't figured out a better way yet.

I'm new to Base and have worked a lot on this and made good progress. But now I've reached a stopping point here. If I can't figure this out, the only way I can think of to proceeed further is to avoid subforms by combining all the tables into one, but that seems needlessly complicated. I really hope someone can point me in the right direction, and I really appreciate any help.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-08-16 20:11:31 +0200

Ratslinger gravatar image

updated 2016-08-19 20:42:34 +0200

Forms and subforms have nothing to do with the error received. The error indicates a Foreign Key violation. If you have a Master table of "Company", for example, and a secondary table with "Employees", in this secondary table you would have a field of the company name relating back to the Master table "Company". Now you can set a one-to-many relation from "Company" to "Employees" via the company name field.

Now if you enter a new "Employee" record with a company name which is not in the "Company" table you will generate the error.

EDIT:

One thing I dislike about Base is the different use of the word "Form". When you create a new blank "Form" in Base then open it in edit mode then open Navigator on this blank "Form" you will see only a folder names "Forms" (This should be called Sections or something else). When you add anything to this blank page (controls), a Form (Section) with a name of Form is created with the control under it. This can now have a single data source (i.e. table, query). By right clicking on "Forms" in Navigator you can add more of these Sections each with its own data source. However you cannot relate these data sources to each other for automation.

By right clicking on the first "Form" created you can create a "Form" (really a subForm) as a child to the "Form" and now this data source can be related to the data source in its' Parent ("Form") but doesn't have to be. It could be just another data source.

Now all this may have confused you more (hopefully not), I always found it easier to understand by example so I attached one for you. Some Forms are just data entry (OILS, PROPERTIES, QUALITIES). On the top two you will find table controls. By selecting an item within the table on the left (or center) you will see items on the right change. Edit these forms use Navigator to see construction using forms, sub-forms and even sub_sub_forms. Also look at the properties of these forms and sub-forms.

EssentialOilsNew.odb

worklogsFix.odb

edit flag offensive delete link more

Comments

If I understand correctly, then perhaps I am using subforms totally wrong. Does this mean that I can only use subforms if I am going to enter information in a linked field of the main form whose possible values already are listed in the subform's table? I have set up some list boxes, so I know that they work this way. Is it not possible to link subforms (and their tables) to a main form by using the same date? In that case, then I am not sure I understand what subforms and their tables are for.

guanolto gravatar imageguanolto ( 2016-08-16 22:41:50 +0200 )edit

Keep in mind your question is about the key violation. This is a TABLE problem and not a FORM problem.

Ratslinger gravatar imageRatslinger ( 2016-08-17 00:36:48 +0200 )edit

Hi, I'm pretty confused. I've read the Base chapter of the LibreOffice manual, and most of the Base manual, and I tried to understand what I could. I have struggled forward step by step, and now I think maybe I shouldn't using subforms at all but just put many forms together into one. Thanks for the file. I looked it over, and tried to understand what's going on in there. Maybe it will help me gain more insights later. Maybe if you can have a look at what I'm doing: (I'll paste links in the next

guanolto gravatar imageguanolto ( 2016-08-17 01:21:44 +0200 )edit
guanolto gravatar imageguanolto ( 2016-08-17 01:24:15 +0200 )edit

First, do NOT give up on Base because of this. Everyone new to this goes through it. Second, these comments are purely constructive - do not take them as negative criticism. You need to start over and get one piece at a time working. What you have is all wrong and quite messy. There are way too many tables and screens need much improvement. Study the sample I posted for table relations and sub-forms. Posting actual problem in next comment.

Ratslinger gravatar imageRatslinger ( 2016-08-17 02:42:07 +0200 )edit

Tutoring table - Primary Key is ID. In the tu: client contract Table - date is Primary Key. You set relation of tutoring/date to tu: client contract/date. Since tu: client contract/date is Primary Key (no dups) it is a one-to-many to tutor/date and must be present for you to enter a tutoring record. Your thinking is off. Re-visit setting up keys in tables. Can't advise too much here because I don't know exactly what is to be accomplished. Hope this helps.

Ratslinger gravatar imageRatslinger ( 2016-08-17 02:56:00 +0200 )edit

Okay. Thanks. I will try doing that. I have the same difficulty when trying to understand Base based on someone's sample table -- I just can't figure out what they're doing, so I don't learn from their example. So, in each form I have start and stop times. I will make queries to calculate time segments and total times by category and in total. I also intend to add query results to the form so I can see the running totals of time, overall dollars per hour, etc.

guanolto gravatar imageguanolto ( 2016-08-17 03:01:40 +0200 )edit

Good that this website is back on line now. I tried changing the primary key to date in the tutoring table, but I am still getting the same error message when I try to save fields in the tutoring form. I just can't seem to get subforms to work. I guess I'll have to just combine the various tutoring subtables into one tutoring table. At least that will mean I only have to enter the date once. You say my thinking is off, but it would be more accurate to say that I have no idea what's going on.

guanolto gravatar imageguanolto ( 2016-08-18 22:34:42 +0200 )edit

You've only changed half the problem. The other problem is in tu: client contact. Change the primary key here to ID not date. Link the two by assigning a foreign key by running the following in Tools->SQL... from the main menu:

ALTER TABLE "tu: client contact" ADD CONSTRAINT fk_date_name FOREIGN KEY ("date") REFERENCES "tutoring" ("date")
Ratslinger gravatar imageRatslinger ( 2016-08-18 22:49:07 +0200 )edit

Also, before changing that primary key, delete the old relationship between the two tables.. It also appears you did did not follow my advice to start over - just get one thing working at a time and then add on. You're still working with multiple tables and sub-forms.

Ratslinger gravatar imageRatslinger ( 2016-08-18 23:02:22 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-08-16 19:26:11 +0200

Seen: 1,669 times

Last updated: Aug 19 '16