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?.