Integrity constraint violation

English
, , ,
#1

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.

#2

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

#3

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.

#4

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

#5

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

#6

my database
an error message

#7

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.

#8

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.

#9

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.

#10

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.

#11

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")
#12

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.

#13

Okay. I’ve taken those additional steps. Now when I try to save a record, I get the error message “Attempt to insert null into a non-nullable column” – earlier I solved this by setting the ID column to Autovalue: Yes. I’m not allowed to do so for the parent table, but I made this change on the subtables. But I’m still getting that error message. I do notice that the Relationships diagram is not showing any relationships between those tutoring tables after I deleted them and ran the SQL command.

#14

As for the sequence of constructing the parts of this database: I have already created the table and form for “cats” and it is working fine. I have done the same with the “other work” table and form. Now I’m focusing on the “tutoring” part of the database and have run into difficulty because I am trying to use subforms. I suspect that I will still have to merge all my tutoring tables into one table to avoid dealing with subforms. After this part of the database, I will do the “translating” part.

#15

Please understand, the question originally posed has been answered. If you have other questions, please ask the as new questions. Further questions can’t be found through searches.

If the answer solves your question please tick the :heavy_check_mark:.

#16

But it’s still not working. I’m still not able to progress to the next step.

#17

All the information and code was given to fix the original question. To go one step further I have added another sample in my original answer - worklogsFix.odb. This covers the problem form/subform and the error you were getting (all based upon the answer and comments). To enter data on the subform, you must have a valid tutoring record (which is why there is navigation bar below the tutoring boxes).

#18

My apologies - I forgot to set tu: client contact ID to auto increment. worklogsFix.odb now corrected. Also the added navigation bar was unnecessary - you can delete.

#19

The navigation bar is pretty nice, actually. And yes, I see that it works now. And the subforms move between records in sync with the main form. Thank you very much for showing me this, ratslinger! What you’ve done here will make it clear what I need to do moving forward from here.

#20

Glad to hear it. As you can see by the sample, working one step at a time eliminates the clutter and confusion.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.