Problems After Appending Data Into Tables

[EmilyMe]

After watching some YouTube videos about Base, I recreated the STRUCTURE of my existing Recipe database into ver. 7.5 Base using Firebird.

My 3 tables looked like this:

My main form is based on tblRecipes, and
tblRecipeIngredients is a Subform that contains the ingredients in the recipe.

I then exported the data from my old database into Calc spreadsheets.
Then I appended this data into my new Base/Firebird structure.

The ID fields in my new tables were set to AutoNumber.
But I copied the ID fields in my OLD tables over into my NEW tables because it was important the numbers match up exactly (many items in the these tables had been deleted through the years, so the ID numbers were no longer exactly sequential).

I thought everything was fine… all my data copied over, and the recipe ID numbers matched up with the appropriate ingredient IDs from from tblRecipeIngredients.

But now I’m getting error messages when I try to add or modify the ingredients in the subform of a recipe, and it won’t save. Because I’m a new member to this group, LibreOffice won’t allow me to upload more than one picture, so the text of the error message is similar to:

firebird_sdbc error:
*violation of PRIMARY or UNIQUE KEY constraint “INTEG_4” on table “tblRecipes”
*Problematic key value is (“fldRecipeID” = 1187)
caused by
‘isc_dsql_execute’

In addition, when I view my tables, the relationships between the tables are now gone.
If I try to recreate the relationships, I get the following error:

firebird_sdbc error:
*violation of FOREIGN KEY constraint “INTEG_25” on table “tblRecipeIngredients”
*Foreign key reference target does not exist
*Problematic key value is (“fldRI_RecipeID” = 374)
caused by
‘ALTER TABLE “tblRecipeIngredients” ADD FOREIGN KEY (“fldRI_RecipeID”)
REFERENCES “tblRecipes” (“fldRecipeID”)’

Does this mean that I will not be able to copy the old data from my previous tables into my new database structure?

[RobertG]

Did you change the values for any primary key? There is no reason to do this. This values should only help to find the exact row of a table and aren’t values for row count. If you delete any row in a table the row count will differ again.

First error: You are trying to create a new entry with a key value 1187, which already exists. If “fldRecipeID” is autovalue: Have a look at max value in this column. Start tools → SQL.

ALTER TABLE "tblRecipes" ALTER "fldRecipeName" RESTART WITH 'max value';

(Set your max value as ‘max value’)

Second error: You have changed the primary keys. There is no “fldRecipeID” in table “tblRecipes”, which has value ‘374’. But every value in “fldRI_RecipeID” need a value in “fldRecipeID”.

So now: Go back to the working version of database. Don’t move it to Calc to get “better” primary key numbers. You will destroy many relations and will get recipes, which wouldn’t make any sense.

[EmilyMe]

Thanks for your attempts to help me, RobertG. I am new to this, and did not understand everything you said.
+

You told me to “Go back to the working version of the database.”

Unfortunately, there is no “working database” in Base. My OLD working database was in Microsoft Access, and I exported all the data to Calc. So I have a new empty BASE database, and all the data is in Calc tables… I need to combine the two.
+
I did not change any of the ID numbers in the Calc sheets on purpose, but maybe it happened accidentally. Do you think it would resolve the errors if I go back and export the tables out of Access again?
+
What I don’t understand: How do I copy and paste the data from Calc into Base in such a way that will retain the OLD ID numbers that establish the proper relationships between the tables? In my last attempt, I copied over any AutoValue aspects with the old ID numbers… was that wrong?

[RobertG]

If you haven’t touched any value of primary key this should work as expected. You could inset primary key without any problem. AutoValue will only start if you have decide to set no value for primary key:

Set fldIngredientID to AutoValue. Copy all content for tblIngredients into this table. If there is any “Problematic key value”: Have a look if there are duplicates for fldIngredientID, special the value, which has been reported.

Do the same for tblRecipes.

Now one hint for zblRecipeIngeredients: Why did you create a key fldRecipeIngredientsID? Combination of fldRI_RecipeID and fldRI_IngredientID will do what you want. You could mark thisd both fields in table editor an set this both fields together as primary key. But it will also work to set a separat AutoValue …

Copy content for tblRecipeIngredients into the table.

Connect all this tables as seen in the screenshot. If you get a message like you got (Foreign key doesn’t exist) have a look at this value. Seems there isn’t any value fldRecipeID = 374 in table tblRecipes, but there is a value fldRI_RecipeID = 374 in tblRecipeIngredients.
You could create a new entry in tblRecipes or you could delete entries for fldRI_RecipeID = 374 in tblRecipeIngredients (in Calc table).

[EmilyMe]

I still got the error message about the “Problematic Key Value” of 374 when I tried to create my relationship between tblRecipes and tblRecipeIngredients. So I examined both tables:

• tblRecipes - fldRecipeID DID contain a record for 374.
• tblRecipeIngredients - fldRI_RecipeID DID contain a record for 374.

Because I was expecting 374 to be missing in one of these tables, but found them in BOTH tables anyway, I deleted the 374 record in BOTH tables (in Calc)… then reimported the data again.
I then verified there were NO more 374 records by sorting on these fields in both tables.
However, I’m still getting the same error message about the missing foreign key 374 when I try to establish the relationship:

Any more thoughts or ideas? I don’t understand this error when I’ve deleted all records for 374 in both tables…

[RobertG]

@EmilyMe

Any more thoughts or ideas?

Without the special database - no. You could send me the database per private mail, so I may have a look.

[EmilyMe]

How would I send it?

[Villeroy]

@EmilyMe

I am new to this

And that’s the problem. LO Base and its “wizards” do not do you any favor. These tools are underdeveloped and almost useless.
I guess, you copied some data from a spreadsheet, pasted to a Base window and let the program do the rest This is ALWAYS a mistake. This way, you never get a working database. Apart from the ID column not being auto-incremental, there might be a lot more problems with that table and with your whole database design. FIRST you need to design your database manually, before you paste anything.

[Wanderer]

@Villeroy

I guess, you copied some data from a spreadsheet

No need to guess:

@EmilyMe

then exported the data from my old database into Calc spreadsheets.
Then I appended this data into my new Base/Firebird structure.

But with Access as source I don’t see where Calc would alter the data to cause this error.

[Villeroy]

@Wanderer

But with Access as source I don’t see where Calc would alter the data to cause this error.

You copy the cell range, paste into the tables section, choose “Create new table with data”, enter a table name, confirm the columns (by default all text), confirm that you want to add an ID column because there is no primary key. The new ID column is always non-incremental. Then you let the wizard create an (almost) useless input form which tries to add new records without primary key. Since 2006 (OOo 2.0) the Base component “works” like this.

If you use the Access database as a direct source for the Base document, there may not be any primary key because Access somhow simulates a missing primary key (if I remember correctly).

[EmilyMe]

@Villeroy and @Wanderer @RobertG
+
But I DID design my empty Firebird database first, not using the Wizard, with AutoValue primary keys, and established my table relationships.
+
Next I exported the two tables (tblRecipes and tblRecipeIngredients) from Access into Calc.
+
Then I appended the data from Calc into the new empty Base/Firebird database.
+
That’s when I started getting errors and “lost” the relationship between tblRecipes and tblRecipeIngredients.
+
Now, even though I deleted ALL the data in tblRecipes and tblRecipeIngredients, I STILL got the following error message when I try to recreate the relationship between tblRecipes and tblRecipeIngredients:

[Villeroy]

The error message describes the problem clearly. You can’t add a reference if that reference is violated right from the start due to inconsistent values. There is no value 374 in the primary key field of the referenced table “tblRecipes”, but there is a 374 in “fldRI_RecipeID” of the referencing table.

[EmilyMe]

@Villeroy and @UnklDonald
+
That would make sense… BUT upon checking the data, there WAS a value 374 in the primary key field of tblRecipes. That recipe, however, had only a blank record in its corresponding #374 record in tblRecipeIngredients.
+
So… to troubleshoot, I deleted BOTH fldRecipeID’s #374 record in tblRecipes, as well as the blank record for its #374 counterpart for fldRI_RecipeID in tblRecipeIngredients. Then I saved, opened/closed the database several times. But I still got the SAME error message about a problematic #374 value when I tried to recreate the table relationship between tblRecipes and tblRecipeIngredients. It doesn’t make any sense to me.
+
That’s when I deleted ALL the records in both tables, and tried to recreate the table relationship first… and got the most recent error about a problematic key value of 1.

[UnklDonald]

The most recent error message you show is reporting you have a record in tlbRecipeIngredients where fldRI_RecipeID has a value of 1, but there is no corresponding record in tlbRecipes

[RobertG]

Problem could be solved. @EmilyMe send the database per private mail. The definition of the table had a field in tblRecipes, which is VARCHAR(500), but the content for this row in this field has been a little bit more, about 540 characters. I changed it in the database to VARCHAR(800) and all should work now. Relationship could be established.

Have written a bug description for a better dialog. Dialog for importing data only says thet there is an error with a field, which is too long. But the table in the database had more than 80 fields…
Bug 154805

[EmilyMe]

@RobertG
+
THANK YOU for your kind assistance with my error messages. After running your instructions for the SQL code to restart max value, I’m getting no more error messages (fingers crossed)!

[Wanderer]

@RobertG

changed it in the database to VARCHAR(800) and all should work now.

So some rows were not imported, wich then leads to the violation at FOREIGN KEY?
Then this check/information was false for some reason:

@EmilyMe

tblRecipes - fldRecipeID DID contain a record for 374.

[EmilyMe]

@Wanderer and @RobertG
+
Yes, the original Calc files exported from Access DID contain a record for 374 in both tables… BUT, since it was presenting a problem, I deleted all 374 records in both tables and Calc files BEFORE I emailed them to @RobertG.
+
But, everything seems to be working great now (fingers crossed), after I followed @RobertG 's instructions to restart the max value in both tables.
+
Everyone’s help is MUCH appreciated!