Skip to content
Advertisement

SQLite Delete row from Table A when columns are referenced as FOREIGN KEYS in other Tables B, C, etc

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?

Advertisement

Answer

You can’t delete a row that is referenced by a row in another table – that’s one of the feature 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,
    ...
);
    
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement