Related records not being deleted

André_van_Haren · July 24, 2023, 12:08pm

Whenever I delete a row from listbox, the related records from the other tables are not being deleted from the database. When I delete a record in the DB Browser program, it works correctly: Deleting a record from the man table also deletes all the related records in the other tables. So the problem must be in the Xojo code: This is the code that deletes a record from the main table:

Is there anything in this code that is not correct?

Try
  //Delete selected story name from database.
  var index_selected as integer
  var StoryID_Selected as integer = ListBoxStories.CellValueAt(ListBoxStories.SelectedRowIndex,1) 

  if index_selected >= 0 then
    app.db.ExecuteSQL("DELETE FROM Stories WHERE ID = " + StoryID_Selected)
else
    MessageBox("Sorry, but there are NO Stories left to remove.")
  end if
  
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
  //If the TABLE does not exist, you will get an error
End Try

Beatrix_Willius · July 24, 2023, 12:10pm

What is index_selected supposed to do? It’s only declared but not changed. Don’t you want to use StoryID_Selected?

André_van_Haren · July 24, 2023, 12:17pm

Honestly, I can not figure out at this moment why I added that code. It could be that I could use StoryID_Selected instead yes. I will have to check this, but I don’t think is related to related records not being deleted.

Emile_Schwarz · July 24, 2023, 12:20pm

Can you share the definition of that TABLE ? (CREATE TABLE Stories…)

André_van_Haren · July 24, 2023, 12:22pm

You mean the database structure of the Stories table?

Emile_Schwarz · July 24, 2023, 12:24pm

From sqlite.org:
CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);

or (for example):

either are good.

André_van_Haren · July 24, 2023, 12:25pm

This is the main table of the database, the Stories table:

CREATE TABLE "Stories" (
	"ID"	INTEGER UNIQUE,
	"Story_Title"	TEXT NOT NULL,
	"Story_Author"	TEXT,
	"Story_Genre"	TEXT,
	"Story_DateCreation"	INTEGER,
	"Story_Source"	TEXT,
	"Story_Notes"	TEXT,
	"Story_Story_Concept"	TEXT,
	"Story_Plot"	TEXT,
	"Story_OrdinWorld"	TEXT,
	"Story_EOWorld"	TEXT,
	"Story_Act1Summ"	TEXT,
	"Story_Act2Summ"	TEXT,
	"Story_Act3Summ"	TEXT,
	"Story_Step1"	TEXT,
	"Story_Step2"	TEXT,
	"Story_Step3"	TEXT,
	"Story_Step4"	TEXT,
	"Story_Step5"	TEXT,
	"Story_Step6"	TEXT,
	"Story_Step7"	TEXT,
	"Story_Step8"	TEXT,
	"Story_Step9"	TEXT,
	"Story_Step10"	TEXT,
	"Story_Step11"	TEXT,
	"Story_Step12"	TEXT,
	"Story_SummStep1"	TEXT,
	"Story_SummStep2"	TEXT,
	"Story_SummStep3"	TEXT,
	"Story_SummStep4"	TEXT,
	"Story_SummStep5"	TEXT,
	"Story_SummStep6"	TEXT,
	"Story_SummStep7"	TEXT,
	"Story_SummStep8"	TEXT,
	"Story_SummStep9"	TEXT,
	"Story_SummStep10"	TEXT,
	"Story_SummStep11"	TEXT,
	"Story_SummStep12"	TEXT,
	"Story_Step1ChapterList"	TEXT,
	"Story_Step2ChapterList"	TEXT,
	"Story_Step3ChapterList"	TEXT,
	"Story_Step4ChapterList"	TEXT,
	"Story_Step5ChapterList"	TEXT,
	"Story_Step6ChapterList"	TEXT,
	"Story_Step7ChapterList"	TEXT,
	"Story_Step8ChapterList"	TEXT,
	"Story_Step9ChapterList"	TEXT,
	"Story_Step10ChapterList"	TEXT,
	"Story_Step11ChapterList"	TEXT,
	"Story_Step12ChapterList"	TEXT,
	"WritingAreaStep1"	TEXT,
	"WritingAreaStep2"	TEXT,
	"WritingAreaStep3"	TEXT,
	"WritingAreaStep4"	TEXT,
	"WritingAreaStep5"	TEXT,
	"WritingAreaStep6"	TEXT,
	"WritingAreaStep7"	TEXT,
	"WritingAreaStep8"	TEXT,
	"WritingAreaStep9"	TEXT,
	"WritingAreaStep10"	TEXT,
	"WritingAreaStep11"	TEXT,
	"WritingAreaStep12"	TEXT,
	"chkOrdinaryWorldActivityStep1"	TEXT DEFAULT 'false',
	"chkInteractionWHelpersStep1"	TEXT DEFAULT 'false',
	"chkForshadowingStep1"	TEXT DEFAULT 'false',
	"chkHeroPersRevealedStep1"	TEXT DEFAULT 'false',
	"chkBeliefInnerNeedsStep1M"	TEXT DEFAULT 'false',
	"chkForshadowingStep1M"	TEXT DEFAULT 'false',
	"chkHeroPersRevealedStep1M"	TEXT DEFAULT 'false',
	"chkInteractionWHelpersStep1M"	TEXT DEFAULT 'false',
	"chkOrdinaryWorldActivityStep1M"	TEXT DEFAULT 'false',
	"chkCallToAdventureStep2"	TEXT DEFAULT 'false',
	"chkFirstReactionToCallStep2"	TEXT DEFAULT 'false',
	"chkResponseStep2"	TEXT DEFAULT 'false',
	"chkCall2AdventureAgreementStep3A"	TEXT DEFAULT 'false',
	"chkCall2AdventureRejectionStep3A"	TEXT DEFAULT 'false',
	"chkMeetingHelpersStep3A"	TEXT DEFAULT 'false',
	"chkMindChangingEventStep3A"	TEXT DEFAULT 'false',
	"chkCall2AdventureAgreementStep3M"	TEXT DEFAULT 'false',
	"chkCall2AdventureRejectionStep3M"	TEXT DEFAULT 'false',
	"chkMeetingMentorStep3M"	TEXT DEFAULT 'false',
	"chkMindChangingEventStep3M"	TEXT DEFAULT 'false',
	"chk1stMeetingAntagonistStep4"	TEXT DEFAULT 'false',
	"chkElementofDangerStep4"	TEXT DEFAULT 'false',
	"chkPickupofHelperStep4"	TEXT DEFAULT 'false',
	"chkPlanningAdventureStep4"	TEXT DEFAULT 'false',
	"chkAntaHelperActStep5"	TEXT DEFAULT 'false',
	"chkAntGoalsBecomeClearStep5"	TEXT DEFAULT 'false',
	"chkCrossingThresholdStep5"	TEXT DEFAULT 'false',
	"chkHeroAwareofAntStep5"	TEXT DEFAULT 'false',
	"chkHeroReaction2EOWorldStep5"	TEXT DEFAULT 'false',
	"chkAntHelpersTestsStep6A"	TEXT DEFAULT 'false',
	"chkHeroLeanrsAboutEOWorldStep6"	TEXT DEFAULT 'false',
	"chkAntHelpersTestsStep6M"	TEXT DEFAULT 'false',
	"chkHeroStartsUnderstandInnerNeedStep6M"	TEXT DEFAULT 'false',
	"chkSelfAnnihilationStep6M"	TEXT DEFAULT 'false',
	"chkStoryChangeAroundHeroStep6M"	TEXT DEFAULT 'false',
	"chkDiscEnemiesDoingStep7A"	TEXT DEFAULT 'false',
	"chkFatherAtonementStep7A"	TEXT DEFAULT 'false',
	"chkTestbyAntagonistStep7A"	TEXT DEFAULT 'false',
	"chkTestbyAntHelpersStep7A"	TEXT DEFAULT 'false',
	"chkFatherAtonementStep7M"	TEXT DEFAULT 'false',
	"chkInnerNeedUnderstandingStep7M"	TEXT DEFAULT 'false',
	"chkTestbyAntagonistStep7M"	TEXT DEFAULT 'false',
	"chkTestbyAntHelpersStep7M"	TEXT DEFAULT 'false',
	"chkNearTriumpAntagonistStep8A"	TEXT DEFAULT 'false',
	"chkPersonalCrisisHeroStep8A"	TEXT DEFAULT 'false',
	"chkHeroforcedbetweenLifestylesStep8M"	TEXT DEFAULT 'false',
	"chkLearningKnowAntagonistPersonallyStep8M"	TEXT DEFAULT 'false',
	"chkNearTriumpAntagonistStep8M"	TEXT DEFAULT 'false',
	"chkPersonalCrisisHeroStep8M"	TEXT DEFAULT 'false',
	"chkAntagonistFalteredHeroWinsStep9A"	TEXT DEFAULT 'false',
	"chkHeroGainsKnowledgeSolveProblemStep9A"	TEXT DEFAULT 'false',
	"chkHeroShowingSuperhumanQualitiesAbilitiesStep9A"	TEXT DEFAULT 'false',
	"chkAllChangingEventStep9M"	TEXT DEFAULT 'false',
	"chkAntagonistCausesHeroChangeStep9M"	TEXT DEFAULT 'false',
	"chkNewBeliefsChoiceStep9M"	TEXT DEFAULT 'false',
	"chkPsychologicalStrenghtStep9M"	TEXT DEFAULT 'false',
	"chkTransformationStep9M"	TEXT DEFAULT 'false',
	"chkConfrontationPreperationAntagonistStep10A"	TEXT DEFAULT 'false',
	"chkConfrontationPreperationHeroStep10A"	TEXT DEFAULT 'false',
	"chkHeroRecountsEventsShortIncidentStep10A"	TEXT DEFAULT 'false',
	"chkConfrontationPreperationAntagonistStep10M"	TEXT DEFAULT 'false',
	"chkConfrontationPreperationHeroStep10M"	TEXT DEFAULT 'false',
	"chkEmotionalAboutTransStep10M"	TEXT DEFAULT 'false',
	"chkHeroKnowsLifeAtStakeStep10M"	TEXT DEFAULT 'false',
	"chkHeroRecountsEventsShortIncidentStep10M"	TEXT DEFAULT 'false',
	"chkAntagonistWinsStep11A"	TEXT DEFAULT 'false',
	"chkChaseMajorPlotDevelopmentStep11A"	TEXT DEFAULT 'false',
	"chkDeathHeroAntagonistStep11A"	TEXT DEFAULT 'false',
	"chkHeroDisplaysTransformationStep11A"	TEXT DEFAULT 'false',
	"chkHeroWinsStep11A"	TEXT DEFAULT 'false',
	"chkLifeOrDeathStruggleStep11A"	TEXT DEFAULT 'false',
	"chkDeathHeroAntagonistStep11C"	TEXT DEFAULT 'false',
	"chkFilledWActionStep11C"	TEXT DEFAULT 'false',
	"chkHeroResolvesStoryProblemStep11C"	TEXT DEFAULT 'false',
	"chkHeroShowsNewPersonaStep11C"	TEXT DEFAULT 'false',
	"chkDeathHeroAntagonistStep11M"	TEXT DEFAULT 'false',
	"chkFilledWTragicActionStep11M"	TEXT DEFAULT 'false',
	"chkHeroResolvesStoryProblemStep11M"	TEXT DEFAULT 'false',
	"chkHeroShowsNewPersonaStep11M"	TEXT DEFAULT 'false',
	"chkGoodbyHookStep12A"	TEXT DEFAULT 'false',
	"chkHappyEndingStep12A"	TEXT DEFAULT 'false',
	"chkResolutionOfConflictStep12A"	TEXT DEFAULT 'false',
	"chkReturnToOrdinaryWorldStep12A"	TEXT DEFAULT 'false',
	"chkHeroUltTransfStep12M"	TEXT DEFAULT 'false',
	"chkLearnedMoralsStep12M"	TEXT DEFAULT 'false',
	"chkRetunToOrdWorldStep12M"	TEXT DEFAULT 'false',
	"chkTragicEndingsStep12M"	TEXT DEFAULT 'false',
	"chkHappyEndingsStep12C"	TEXT DEFAULT 'false',
	"chkHeriInBalanceStep12C"	TEXT DEFAULT 'false',
	"chkHeroNewPersonStep12C"	TEXT DEFAULT 'false',
	"chkLearnedMoralsStep12C"	TEXT DEFAULT 'false',
	"chkReturnOrdWorldStep12C"	TEXT DEFAULT 'false',
	"PopupMenuTypes"	TEXT,
	"PopupMenuCTypes"	TEXT,
	"PopupMenuMTypes"	TEXT,
	"PopMenuCategory"	TEXT,
	"ClockCatType"	TEXT,
	PRIMARY KEY("ID")
);

André_van_Haren · July 24, 2023, 12:26pm

And this is one of the related tables, Events:

CREATE TABLE "Events" (
	"ID"	INTEGER UNIQUE,
	"TextEventDescription"	TEXT,
	"TextEventTitle"	TEXT,
	"EventDate"	INTEGER,
	"FKStory"	INTEGER,
	PRIMARY KEY("ID"),
	FOREIGN KEY("FKStory") REFERENCES "Stories"("ID") ON DELETE CASCADE
);

Emile_Schwarz · July 24, 2023, 12:28pm

So, at ListBox populated time, you store the value of ID in ListBoxStories.CellValueAt(ListBoxStories.SelectedRowIndex,1)…

Jeff_Tullin · July 24, 2023, 12:28pm

Don’t you need the constraint on the Stories table, rather than the events?

You can delete an event and the story remains.
If you delete a story, it cannot have events any longer

Beatrix_Willius · July 24, 2023, 12:29pm

The table is in need of normalising.

Emile_Schwarz · July 24, 2023, 12:36pm

I would use two different ID… like Stories_ID and Event_ID (even if only avoid my confusion at later read time).

Martin_T · July 24, 2023, 12:39pm

ListBox.CellValueAt is of type String and not Integer.

André van Haren:

Try
  //Delete selected story name from database.
  var index_selected as integer
  var StoryID_Selected as String = ListBoxStories.CellValueAt(ListBoxStories.SelectedRowIndex,1) 

  if index_selected >= 0 then
    app.db.ExecuteSQL("DELETE FROM Stories WHERE ID = " + StoryID_Selected)
else
    MessageBox("Sorry, but there are NO Stories left to remove.")
  end if
  
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
  //If the TABLE does not exist, you will get an error
End Try

André_van_Haren · July 24, 2023, 1:32pm

Thanks, I changed it to integer and created a Global StoryID_Selected property instead, deleting the once in the code. Didn’t fix the problem.

André_van_Haren · July 24, 2023, 1:32pm

Yes, that could be clearer, but I wonder if it would fix the problem. I will change them though. Thanks.

André_van_Haren · July 24, 2023, 1:34pm

No, I don’t think so. Also, deleting the Story record from the table in DB Browser does correctly delete the related records in the 4 other tables, so this must be correct. It however does not work when running the delete Story Record from inside Xojo.

Maximilian_Tyrtania1 · July 24, 2023, 2:15pm

This:
FOREIGN KEY(“FKStory”) REFERENCES “Stories”(“ID”) ON DELETE CASCADE
…should guarantee that the related records are deleted. What I suspect is happening is that your code fails to delete the Stories-record in the first place.

AlbertoD · July 24, 2023, 2:26pm

Questions:

Do you get an error?
What is the value of StoryID_Selected before you execute DELETE?
Does the Story with the ID gets deleted but not the related Events? or nothing gets deleted?
Can you share a sample project? Maybe someone can take a look and find the problem faster this way.

André_van_Haren · July 24, 2023, 2:38pm

Yes, the code deletes the record in the Stories Table, I can see this in the database file in DB Bowser. But the character table records are still there in this case.

AlbertoD · July 24, 2023, 3:15pm

Previously you talked (and showed code) for Stories and Events tables. What is this ‘Character table’?