0

I have two really simple tables, without any constraint between them:

CREATE TABLE groups (groupId INTEGER PRIMARY KEY AUTOINCREMENT, groupName TEXT)

CREATE TABLE savedRuns (runId INTEGER PRIMARY KEY AUTOINCREMENT, runName TEXT, groupId INT NULL, startTime TEXT, stopTime TEXT, elapsedMilliseconds INT)

Whenever I delete an entry from the group table, the entry in savedRuns which has the same groupId is also deleted. I do not want that!

This is the delete query:

DELETE FROM groups WHERE groupId = 1

I can't understand this behavior as there are no foreign key constrains between the two tables!

I'm using SQlite from the expo-sqlite library in my React Native app.

My final goal is to enabled foreign keys and set the groupId to null in the savedRuns table, but I think I should figure out the first problem first.

I even ran PRAGMA foreign_keys and got 0 as result

remyremy
  • 3,548
  • 3
  • 39
  • 56
  • If you are sure that *there are no foreign key constrains between the two tables* then check for triggers. – forpas May 16 '21 at 14:23
  • `select * from sqlite_master where type = 'trigger'` returns an empty array. I guess there wouldn't be any trigger until I create them anyways? – remyremy May 16 '21 at 14:30
  • The behavior that you describe is not consistent with the table definitions in your question. Cascaded deletes happen only when they are enforced by foreign key constraints or triggers. – forpas May 16 '21 at 14:39
  • yes, that is my understanding as well. I will investigate further... maybe something wrong with my Delete function... – remyremy May 16 '21 at 14:45

1 Answers1

0

All the queries above were fine. The problem was with my SELECT query which didn't display the entries without a groupId in the groups table.

Changing the INNER JOIN to LEFT JOIN in my SELECT query fixed the issue!

remyremy
  • 3,548
  • 3
  • 39
  • 56