1

During the development of an android application I have noticed that the ON UPDATE CASCADE is not working when a NULL field is updated. However, when the field is a NON NULL value the ON UPDATE CASCADE works just fine.

I searched online but couldn't find a specific answer.

My class extends the SQLiteOpenHelper and I have the override onOpen method where I activate the foreign key constraint. I don't believe this is where the issue lies as it's just not working when updating a NULL field.

@Override
public void onOpen(@Nonnull final SQLiteDatabase db) {
    super.onOpen(db);
    if (!db.isReadOnly()) {
        // Enable foreign key constraints
        db.execSQL("PRAGMA foreign_keys=ON;");
    }
}

The schema I am using is:

CREATE TABLE IF NOT EXIST users (
    role_id INTEGER,
    user_id NUMERIC,
    username TEXT,
    PRIMARY KEY(user_id)
)

CREATE TABLE IF NOT EXIST devices (
    user_id NUMERIC,
    device_id NUMERIC,
    device_name TEXT,
    PRIMARY KEY(device_id),
    FOREIGN KEY(user_id) REFERENCES users(user_id)
        ON UPDATE CASCADE
)

INSERT INTO users (role_id, user_id, username) VALUES (1, NULL, 'test')
INSERT INTO devices (user_id, devices, device_name) VALUES (NULL, NULL, 'test')

When we now update this user row and replace the NULL with 1 the update is not being cascaded to the device row.

Stedy
  • 7,359
  • 14
  • 57
  • 77
Petros Paraskevopoulos
  • 11
  • 6
  • `NULL` does not equal `NULL` so from a SQL standpoint, the rows are not considered to be related. See also: [why is null not equal to null false](http://stackoverflow.com/questions/1833949/why-is-null-not-equal-to-null-false) – chue x Jun 03 '14 at 17:56
  • So, although `NULL` is supported as foreign key and rows that contain a `NULL` foreign key can be inserted without any problem, that doesn't mean that those actually could be associated with the parent table? And that isolates those rows from the foreign key constraint altogether, is that right? – Petros Paraskevopoulos Jun 03 '14 at 21:11
  • Also, although a table that contains a foreign key constraint would complain on inserting a foreign key that does not exist on the parent table, that does not mean that when inserting a row with a `NULL` foreign key would result to the same behavior, rather that is allowed (even if the parent table is empty). – Petros Paraskevopoulos Jun 03 '14 at 21:20
  • You are correct in all of your assertions. When using `NULL` in the child table it is not considered related to the parent. `NULL` is a special value and does not apply to the relationship constraint. Therefore you are allowed to insert rows in the child (using NULL keys) without any rows in the parent. The only way to force a relationship with the parent is to declare the child field as not-nullable. – chue x Jun 04 '14 at 03:57
  • Thank you a lot Chue, much appreciated your input. – Petros Paraskevopoulos Jun 04 '14 at 09:50

1 Answers1

0

Answered by "chue x":

When using NULL in the child table it is not considered related to the parent. NULL is a special value and does not apply to the relationship constraint. Therefore you are allowed to insert rows in the child (using NULL keys) without any rows in the parent. The only way to force a relationship with the parent is to declare the child field as not-nullable.

Petros Paraskevopoulos
  • 11
  • 6