0

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

New contributor
Ricardo Balderas is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
0

First, do you have any data in movies_genres?

SELECT * FROM movies_genres;

If not, you can check if the primary key is missing in any of the sources tables

SELECT movie_id FROM movies GROUP BY movie_id HAVING COUNT(1) > 1;

and the same for genres. If above is ok, the error should not happen, but you can check with:

SELECT m.movie_id, g.genre_id 
FROM ( -- I assume the parentheses was a typo and moved it
    SELECT movie_id 
    FROM movies 
    WHERE title = arg1
) AS m
CROSS JOIN ( -- see comment above
    SELECT genre_id 
    FROM genres WHERE title = arg2
) AS g
GROUP BY m.movie_id, g.genre_id 
HAVING COUNT(1) > 1;

You can btw simplify your query to:

SELECT m.movie_id, g.genre_id 
FROM movies m
JOIN genres g
    ON m.title = arg1  
   AND g.title = arg2
| improve this answer | |
  • Hello, sorry for the late feedback but I was so focused on the problem that I forgot it. I seem the issue with the second statement. Btw I simplify my insert statement, thank you so much! – Ricardo Balderas Aug 3 at 21:53

Your Answer

Ricardo Balderas is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.