SQLite Restrict/Update/cascade-CodePudding

"should not be able delete movie if there are any linked data present",
    async done => {
      const movieId = 100;
      const query = 
      `DELETE FROM ${MOVIES}
      WHERE id = ${movieId}`;

"should be able to delete movie",
    async done => {
      const movieId = 5915;
      const query = `DELETE FROM ${MOVIES}
      WHERE id = ${movieId}`;

So i have these two lines but i want to deleate on second and not to deleate on first, i know it has to do with update, cascade, delete, restrict, but i cant figure out or find more info about it right now... back end for this looks like

const CREATE_MOVIE_GENRES_TABLE = `
CREATE TABLE ${MOVIE_GENRES} (
  movie_id integer NOT NULL REFERENCES movies(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  genre_id integer NOT NULL REFERENCES genres(id) ON DELETE RESTRICT,
  Primary key (movie_id, genre_id)
)`;

for more than one MOVIE tables

I know my delete from movies, section is wrong for sure... and i cant figure out right way for create table section as well

CodePudding user response：

In short, what you have appears to be fine.

Assuming that 100 is mapped to one or more genres via the movies_genres table (first) and that 5915 is NOT mapped to any genres via the movies_genres table then:-

the first DELETE FROM movies WHERE id=100 will fail due to the foreign key constraint conflict.
the second DELETE FROM movies WHERE id=5915 will delete the movie.
updating 100 setting the id to 999 e.g. UPDATE movies SET id=999 WHERE id=100 will work AND the update will be propagated to the movies_genres rows where the movie_id is 100 setting it to 999.
updating 5915 setting the id to 55555 e.g. UPDATE movies SET id=55555 WHERE id=5915 will work, there are no rows in movies_genres with movie_id as 5195.

However, if 5915 has mappings and you want to force it's deletion then you would have to delete the mappings from the movie_genres table and then delete 5915 e.g.

DELETE FROM movie_genres WHERE movie_id=5915;

followed by

DELETE FROM movies WHERE id=5915;

Demonstration

To demonstrate the above (noting that the order of updates/deletions suits a single run) :-

DROP TABLE IF EXISTS movie_genres;
DROP TABLE IF EXISTS movies;
DROP TABLE IF EXISTS genres;

CREATE TABLE IF NOT EXISTS movies (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE IF NOT EXISTS genres (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE movie_genres (
  movie_id integer NOT NULL REFERENCES movies(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  genre_id integer NOT NULL REFERENCES genres(id) ON DELETE RESTRICT,
  Primary key (movie_id, genre_id)
);

INSERT INTO movies VALUES (100,'Movie100'),(5915,'Movie5915'),(10,'Movie10');
INSERT INTO genres VALUES (1,'Genre1'),(2,'Genre2'),(3,'Genre3');
INSERT INTO movie_genres VALUES /* map movie 100 genre 1*/ (100,1), /* extras */(10,1),(10,2),(10,3);

UPDATE movies SET id = 55555 WHERE id = 5915;
UPDATE movies SET id = 999 WHERE id = 100;
/* reset id's */
UPDATE movies SET id=5915 WHERE id=5555;
UPDATE movies SET id=100 WHERE id=999;

/* Will delete as 5915 has no genres */
DELETE FROM movies WHERE id = 5915;

/* Reapply 5915 that was deleted and add mapppings to genres */
INSERT INTO movies VALUES (5915,'Movie5915');
INSERT INTO movie_genres VALUES /* map movie 5915 genre 1,2 and 3*/ (5915,1),(5915,2),(5915,3);

/* Force delete 5915 by-passing FK conflict i.e. doing the equivalent of cascade before deletion */
DELETE FROM movie_genres WHERE movie_id=5915;
DELETE FROM movies WHERE id= 5915;

/* DELETE WILL FAIL due to FK conflict */
DELETE FROM movies WHERE id = 100;

Running the above the message log :-

DROP TABLE IF EXISTS movie_genres
> OK
> Time: 0.186s


DROP TABLE IF EXISTS movies
> OK
> Time: 0.12s


DROP TABLE IF EXISTS genres
> OK
> Time: 0.074s


CREATE TABLE IF NOT EXISTS movies (id INTEGER PRIMARY KEY, name TEXT)
> OK
> Time: 0.084s


CREATE TABLE IF NOT EXISTS genres (id INTEGER PRIMARY KEY, name TEXT)
> OK
> Time: 0.122s    

CREATE TABLE movie_genres (
  movie_id integer NOT NULL REFERENCES movies(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  genre_id integer NOT NULL REFERENCES genres(id) ON DELETE RESTRICT,
  Primary key (movie_id, genre_id)
)
> OK
> Time: 0.169s


INSERT INTO movies VALUES (100,'Movie100'),(5915,'Movie5915'),(10,'Movie10')
> Affected rows: 3
> Time: 0.084s


INSERT INTO genres VALUES (1,'Genre1'),(2,'Genre2'),(3,'Genre3')
> Affected rows: 3
> Time: 0.084s


INSERT INTO movie_genres VALUES /* map movie 100 genre 1*/ (100,1), /* extras */(10,1),(10,2),(10,3)
> Affected rows: 4
> Time: 0.084s



UPDATE movies SET id = 55555 WHERE id = 5915
> Affected rows: 1
> Time: 0.084s


UPDATE movies SET id = 999 WHERE id = 100
> Affected rows: 1
> Time: 0.096s

/* reset id's */
UPDATE movies SET id=5915 WHERE id=5555
> Affected rows: 0
> Time: 0s


UPDATE movies SET id=100 WHERE id=999
> Affected rows: 1
> Time: 0.096s


/* Will delete as 5915 has no genres */
DELETE FROM movies WHERE id = 5915
> Affected rows: 0
> Time: 0s


/* Reapply 5915 */
INSERT INTO movies VALUES (5915,'Movie5915')
> Affected rows: 1
> Time: 0.083s


INSERT INTO movie_genres VALUES /* map movie 5915 genre 1,2 and 3*/ (5915,1),(5915,2),(5915,3)
> Affected rows: 3
> Time: 0.096s


/* Force delete 5915 by-passing FK conflict i.e. doing the equivalent of cascade before deletion */
DELETE FROM movie_genres WHERE movie_id=5915
> Affected rows: 3
> Time: 0.107s


DELETE FROM movies WHERE id= 5915
> Affected rows: 1
> Time: 0.084s


/* Nothing to delete due to id change */
DELETE FROM movies WHERE id = 100
> FOREIGN KEY constraint failed
> Time: 0.003s