Categories
Database Development

SQLite constraint violation – How to insert data to this schema?

First of all, sorry for the question but I was hours searching for a solution and the only things that I got are Java exceptions. I’m developing a little web crawler for movies, so I created this two tables.

CREATE TABLE IF NOT EXISTS movies(
    movie_id INTEGER PRIMARY KEY,
    title VARCHAR(100) NOT NULL UNIQUE,
    info VARCHAR(250)
);

CREATE TABLE IF NOT EXISTS genres(
    genre_id INTEGER PRIMARY KEY,
    title VARCHAR(50) NOT NULL UNIQUE,
);

Well, as you can see is a many to many relationship, so I made a junction table like this:

CREATE TABLE IF NOT EXISTS movies_genres(
    movie_id INTEGER NOT NULL,
    genre_id INTEGER NOT NULL,
    PRIMARY KEY(movie_id, genre_id),
    FOREIGN KEY (movie_id) REFERENCES movies(movie_id),
    FOREIGN KEY (genre_id) REFERENCES genres(genre_id)
);

And I’m trying to insert data with (arg are the method arguments):

INSERT INTO movies_genres(movie_id, genre_id) 
SELECT m.movie_id, g.genre_id FROM
SELECT (movie_id FROM movies WHERE title = arg) AS m
CROSS JOIN SELECT (genre_id FROM genres WHERE title = arg) AS g;

But I get this SQLITE_CONSTRAINT] Abort due to constraint violation (UNIQUE constraint failed. I don’t know where is the error, maybe is in the schema but I can’t see the error. Any idea on how fix it?.

Leave a Reply

Your email address will not be published. Required fields are marked *