sqlite

On Delete set null not behaving as expected with sqlite

I'm using sqlite and I have a table Sessions (sessionId INTEGER, ...) And a table Orders (orderId INTEGER, ...,sessionID INTEGER, Foreign key sessionID references Session on delete set null) But when I delete a session that has orders I get an error that says that I'm violationg a foreign key constarint. Did this happen to anyone else ?

Solution

  • It would appear that you have the sessionID in the orders table defined as being NOT NULL as otherwise NULL is an acceptable value and does not result in a foreign key conflict.

    Perhaps consider the following that demonstrates:-

    /* Clean up demo environment just in case*/
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS sessions;
/* Create the tables as per the question */
CREATE TABLE IF NOT EXISTS sessions (sessionID INTEGER PRIMARY KEY, sessionname TEXT /* etc*/);
CREATE TABLE IF NOT EXISTS orders (orderID INTEGER PRIMARY KEY, sessionID INTEGER, FOREIGN KEY (sessionID) REFERENCES sessions(sessionID) ON DELETE SET NULL);
/* Add some osessions*/
INSERT INTO sessions VALUES
    (1,'Session1'),(2,'Sessions2'),(3,'Session3')
;
/* add some orders */
INSERT INTO orders VALUES
    (null,1),(null,1),(null,1),
    (null,2),(null,2),(null,2),(null,2),
    (null,3),(null,3)
;
/*OUTPUT1 show the joined data */
SELECT * FROM sessions JOIN orders ON sessions.sessionid = orders.sessionid;
/* delete a session */
DELETE FROM sessions WHERE sessionid = 2;
/*OUTPUT2 show the resultant joined data */
SELECT * FROM sessions JOIN orders ON sessions.sessionid = orders.sessionid;
/*OUTPUT3 show the orders*/
SELECT * FROM orders;
/* Cleanup the demo*/
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS sessions;
/* Repeat as above BUT using NOT NULL for the sessionid column of the Orders table */
CREATE TABLE IF NOT EXISTS sessions (sessionID INTEGER PRIMARY KEY, sessionname TEXT /* etc*/);
CREATE TABLE IF NOT EXISTS orders (orderID INTEGER PRIMARY KEY, sessionID INTEGER NOT NULL /*<<<<<<<<<<*/, FOREIGN KEY (sessionID) REFERENCES sessions(sessionID) ON DELETE SET NULL);
INSERT INTO sessions VALUES
    (1,'Session1'),(2,'Sessions2'),(3,'Session3')
;
INSERT INTO orders VALUES
    (null,1),(null,1),(null,1),
    (null,2),(null,2),(null,2),(null,2),
    (null,3),(null,3)
;
/*OUTOUT4 */
SELECT * FROM sessions JOIN orders ON sessions.sessionid = orders.sessionid;
DELETE FROM sessions WHERE sessionid = 2; /* NOTE FAILS with FK conflict */
SELECT * FROM sessions JOIN orders ON sessions.sessionid = orders.sessionid;
SELECT * FROM orders;
/* Cleanup demo environment */
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS sessions;

    The results (outputs 1-4)

    OUTPUT1

    OUTOUT2

    OUTPUT3

    OUTPUT4

    At this stage, when attempting the deletion of session 2 the Foreign Key conflict occurs and the message log includes:-

    DELETE FROM sessions WHERE sessionid = 2
> NOT NULL constraint failed: orders.sessionID
> Time: 0s