0

So I have several tables, but will show only 2 as an example:

Table G

gNo | gName | gAddress
G1  | Jean  | Bonalbo
G2  | Oscar | Berlin
G3  | Edd   | Brisbane
G4  | Bob   | California

Table B

gNo | gdate | rNo
G1  | 2019  | R21
G2  | 1993  | R03
G3  | 1989  | R26
G4  | 2020  | R04

Now, I need to DELETE the last row from Table G, but SQLite keeps saying that there is a FOREIGN KEY constraint failure. This is what I've done:

DELETE FROM G WHERE gNo = 'G4';

I know that's not complete. I tried using CASCADE at the end of that line, but it throws ERROR. Then I tried using DELETE FROM G JOIN Table B WHERE gNo = 'G4'; and didn't work either.

I'm very lost here.

Some help?

EddSoul24
  • 31
  • 7
  • 1
    What are your table definitions? The actual `CREATE TABLE` statements, added to your question. – Shawn Aug 29 '20 at 17:44
  • 1
    You can't add foreign key constraints after you create a table in SQLite. You must do it when you create the tables. Check other options here: https://stackoverflow.com/questions/1884818/how-do-i-add-a-foreign-key-to-an-existing-sqlite-table – forpas Aug 29 '20 at 17:51
  • @Shawn this is a database I downloaded from a website. I don't have the sql of how it was crafted. Sorry. – EddSoul24 Aug 29 '20 at 17:59
  • @forpas Thank you, I'm going to check your link. PS: I haven't upvoted the answers, I think the people that post the answers are upvoting them themselves. If not, I don't know what's happening, because that's not me. – EddSoul24 Aug 29 '20 at 18:02
  • `.schema` in the `sqlite3` command line shell will show you the table definitions for a database. – Shawn Aug 29 '20 at 18:13
  • @EddSoul24 Users can't upvote their own posts. – Modus Tollens Aug 29 '20 at 18:20

2 Answers2

3

You can't delete a row that is referenced by a row in another table - that's one of the features of foreign keys.

You would typically need to delete the child(ren) record(s) first:

delete from b where gno = 'G4';
delete from g where gno = 'G4);

Otherwise, you need to change the definition of the foreign key so it includes the on delete cascade option:

create table b (
    gno text references g(gno) on delete cascade,
    gdate int,
    rno text,
    ...
);
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thank you very much. I thought about this solution(first one about deleting the children first), but it seemed too obvious and I thought I needed to use CASCADE, UPDATE or JOIN clauses. This worked, although I think SQLite should have a function that does this more efficiently. About the second suggestion, the database was downloaded from a website, so I'm just trying to modify its content. – EddSoul24 Aug 29 '20 at 18:08
-1

You need to define the foreign key constraint with cascade. You can delete the existing constraint and define the foreign key as:

create table b (
    . . .,
    gno int references g(gno) on delete cascace
);

This tells the database that when you delete a row in the primary table, then to delete the references as well.

Here is a db<>fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 4
    How is this question upvoted? SQLite does not support adding foreign key constraints. – forpas Aug 29 '20 at 17:52
  • @forpas . . . It is interesting then that the documentation disagrees with you: https://sqlite.org/foreignkeys.html. – Gordon Linoff Aug 29 '20 at 19:05
  • 3
    *The only schema altering commands directly supported by SQLite are the "rename table", "rename column", and "add column"* from https://sqlite.org/lang_altertable.html Where exactly did you find the documentation that disagrees with me? – forpas Aug 29 '20 at 19:08