CODEWITHSUNDEEP
androidsqlsqlitesql-delete
user2707175
user2707175

Reputation: 1137

android sqlite deleting record being lined as foreign key with another record

I have the following tables in my sqlite android database:

CREATE TABLE MAIN (
_ID INTEGER PRIMARY KEY ASC AUTOINCREMENT,
NAME TEXT,
.....
CURRENT_CARD INTEGER
FOREIGN KEY (CURRENT_CARD) REFERENCES CARD (_ID));

CREATE TABLE CARD (
_ID INTEGER PRIMARY KEY ASC AUTOINCREMENT,
.....

Now I want to delete records from my CARD table in the following way:

DELETE FROM CARD WHERE _ID IN (10,11,12,...);

Everything works ok until I have the record in Card table being linked to Main table, ex. let's admit that Card with id 11 has the corresponding record in Main table. When deleting Card 11 I would like to set the corresponding CURRENT_CARD field for record ex. 1 in MAIN as null (I don't want to delete the whole record in MAIN, just to null one column).

How to do that in the easiest way? If possible as one sql statement...

Upvotes: 0

Views: 159

Answers (1)

laalto
laalto

Reputation: 152817

Use ON DELETE SET NULL action with the FOREIGN KEY.

Remember to enable foreign key support with pragma foreign_keys='ON'.

Upvotes: 1

Related Questions